CODEWITHSUNDEEP
excel
Da. Hernandez
Da. Hernandez

Reputation: 3

Return value of cell address in column to right or left of a given cell address

I'm using the below formula to return the address of values from sheet2 that match my value in sheet1:

=ADDRESS(MATCH(C18,Sheet2!C:C,0),3)

If the returned result in sheet1 cell E19 is (sheet2) $c$8 (and not null), then I would like to find what value is in sheet2 $h$8.

Upvotes: 0

Views: 2737

Answers (1)

David Zemens
David Zemens

Reputation: 53623

Nest the formulas. Use the Match function to test whether the searched value exists. If it does exist, then the false part of the If statement uses the VLOOKUP to find the corresponding value in column H.

=If(IsError(Match(C18,Sheet2!C:C,False)),"match not found",VLOOKUP(C18,Sheet2!C:H,6,False))

(untested, so double-check that I put the parentheses in the right spots)

With this approach, you can only do a left-to-right search, so for example you couldn't return a value in column A, by searching column C. You could modify this approach using the INDEX function (which I know is possible) or possibly the OFFSET function.

Here is an example of finding a value in corresponding row of Column A, while searching column C, using the Index function (tested & verified):

=INDEX(Sheet2!A:A,MATCH(C18,Sheet2!C:C,FALSE))

You could add the error logic like:

=If(IsError(Match(C18,Sheet2!C:C,False)),"N/A",INDEX(Sheet2!A:A,MATCH(C18,Sheet2!C:C,FALSE))

Upvotes: 1

Related Questions