1

I would like to match cell value to sheet name then return value based on that. How can i do that?

For example, there are 2 column (A and B)

If the value in range A1:A5 = other sheet name Then return cell value of column B in that other sheet name

Thank you in advance.

1 Answer 1

0

If A2 in Sheet1 contains the formula, and B2 contains the sheet name "Sheet2", this will fetch the value in Sheet2!A2:

=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()))

To get the value in Sheet2!B2 instead, you can add 1 to COLUMN():

=INDIRECT(B2&"!"&ADDRESS(ROW(),COLUMN()+1))

Explanation:

  • INDIRECT returns the value stored in a cell using some text formatted as a standard cell reference such as "A2", or "Sheet2!$A$2". Now we will try to generate that text automatically.
  • ADDRESS(row, col) converts cell and column numbers to a standard cell reference in text format. ADDRESS(1,1) returns "$A$1", and so on.
  • ROW() returns the number of the row the formula sits in, and COLUMN() for the column number. Since the formula is in A2, ROW() will equal 2 and COLUMN() will equal 1. These are just plain numbers, so we can add 1 to the column number to get B instead of A. We give those numbers to ADDRESS(2,2) to get "$B$2".
  • We append this address to the sheet name we want that is in B2: B2&!"&ADDRESS(ROW(),COLUMN()), not forgetting the exclamation mark between, to obtain the full cell reference we need: "Sheet2!$B$2"
  • Last step, we provide that text to INDIRECT, which will go and fetch the value at that address.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .