How can I reference other sheets by column value?

user_6018651 0
Viewed 29

This is the scenario:

Sheet1:

A      B
name   age
Jack   40
Alice  50
Bob    60

Sheet2:
A        B
item     Jack
Sheet1   40 <- use formula here

In Sheet2, A column has the sheet name (e.g. 'Sheet1') and row 0 has the name (e.g.'Jack'), I would like get 40 in B2 cell with a formula! Just like query from Sheet1 with name Jack and get the age value!

BTW, Sheet1 and Jack is variable, maybe changed so Sheet1!A1 syntax will not good!

vlookup google-sheets-formula google-sheets arrays
1 Answers

use:

=IFNA(VLOOKUP(B1, INDIRECT(A2&"A:B"), 2, 0))

where B1 is your Jack and A2 is your Sheet1

user_56326290
Related