How to get criteria column dynamically using INDEX+VLOOKUP in Sheets?

user_11832197 0
Viewed 48

One good soul has helped me get to this point, but I got one more contraint I'm trying to tackle:

How to populate Rep and Region based on the ID column, but this column may move, so getting this must be dynamic, just like getting Rep and Region columns is.

Here's the working formula, without the criteria:

=INDEX(VLOOKUP(ROW('From Sheet1'!A7:A); {ROW('From Sheet1'!A7:A)\ 'From Sheet1'!B7:C}; MATCH({"Rep"\ "Region"}; 'From Sheet1'!A7:C7; 0); 0))

Here's a sample data.

Appreciate your help, as usual!

vlookup google-sheets-formula google-sheets match arrays
2 Answers

This one is a doozy

=ARRAYFORMULA(VLOOKUP(A7:A,{INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("ID",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("ID",'From Sheet1'!7:7,0),4))),INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("Rep",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("Rep",'From Sheet1'!7:7,0),4))),INDIRECT("'From Sheet1'!"&LEFT(ADDRESS(7,MATCH("Region",'From Sheet1'!7:7,0),4))&"7:"&LEFT(ADDRESS(7,MATCH("Region",'From Sheet1'!7:7,0),4)))},{2,3}))

In the place of that formula you had in cell B7, This is also assuming your headers are in row 7 on sheet1.

user_156275900

try:

=INDEX(IFNA(VLOOKUP(A7:A, 
 {FILTER('From Sheet1'!A7:Z, 'From Sheet1'!7:7="ID"), 'From Sheet1'!B7:C}, 
 MATCH({"Rep", "Region"}, 'From Sheet1'!A7:C7), 0)))

enter image description here

user_56326290
Related