New Bug Encountered: Bug using VLOOKUP Google Sheet formula

[Facebairro]

Guys, I am using VLOOKUP function like this:

=VLOOKUP($B2,Produtos!$B$1:$Q,16,FALSE)

When I click on REGENERATE STRUCTURE button, the Spreadsheet formula is:

VLOOKUP(RC2,Produtos!R1C2:RC17,16,FALSE)

But, when I insert a new register on Google Sheet Table, the formula of the new regisrter column has been changed to:

=VLOOKUP($B3,Produtos!$B$1:$Q3,16,FALSE)

So, the formula is putting the number of line ($Q3) instead of ($Q) only, and the data is not founded.

Someone could help me?

[Kirk_Masden]

Hi! Welcome to the community!

I’m not sure but perhaps AppSheet doesn’t handle the $Q well. An expression like $Q (where the final row number is left off) says “to the last row.” If AppSheet doesn’t understand the “to the last row” intent, perhaps the issue might be resolved on the Google sheet side. Here’s an idea:

1. On another sheet (one that isn’t a “Table” in your app) put a formula like the following in a cell:
   =countif(Sheet1!A1:A,"="&"")+countif(Sheet1!A1:A,"<>"&"")
   This will give you the number of rows in “Sheet1”. If new rows are added, the number will change appropriately. In my practice sheet, this formula is in A1 of “Sheet2”.

2. Use “indirect” to identify the range in your vlookup expression.
   Here’s my test formula:
   =vlookup(3,indirect(concatenate(“K2:L”,Sheet2!A$1)),2,false)
   In my sheet this produced the same result that =vlookup(3,K2:L1000,2,false) would have produced. But, if new rows get added to the table “L1000” will become “L1001” or whatever.

I haven’t tried this will AppSheet but I’m guessing there’s a good chance it will work.

I wonder if anyone knows a better, more efficient way of identifying the last row in a page.

[Kirk_Masden]

I think there may be a simpler way, after all. Instead of using the complicated

=countif(Sheet1!A1:A,"="&"")+countif(Sheet1!A1:A,"<>"&"")

expression to find the exact number of the last row, just type in a number like 10000 (or maybe 50000) – a number that is a lot bigger than the current number of rows in the sheet and one that you can’t imagine your sheet will ever outgrow. That should work. I just tried it and it did.

Actually, now I see that indirect wasn’t even necessary:

=vlookup(3,$K$2:$L$10000,2,false)

This seems to be perfectly adequate.

[hapham]

I facing a new problem. When I import a new row I want my formula to change automatically like this:
Formula Cell on Row 1: =vlookup(K2,$K$2:$L$10000,2,false)
Formula Cell on Row 2: =vlookup(K3,$K$2:$L$10000,2,false)
Formula Cell on Row 3: =vlookup(K4,$K$2:$L$10000,2,false)
…
Formula Cell on Row n: =vlookup(Kn,$K$2:$L$10000,2,false)
How do I validate field K?