excel lookup value and return cell reference

0
votes

Hi I want to write a excel formula that can let me find a column of sheet 1 value from sheet 2, then return me the cell reference from sheet 2,

for example, the lookup values are in Column A in sheet1, the range is A1:AX131 in Sheet 2,

what formula I can use to return the sheet 2 cell reference?

Thanks,

2
excel
nothing works for search across multi-columns and rows – user2864813

2 Answers

0
votes

The formula to return the cell reference is ADDRESS( row, column, [ref_type], [ref_style], [sheet_name] ) Source: http://www.techonthenet.com/excel/formulas/address.php

As for searching an array, there is no specific formula to conduct an array lookup. If you can sort your information so that it is all in one column or row, you can use an index-match formula to find the cell you're looking for. Google "index match" for examples.

-1
votes

With using cell,index and match, this task could be done.

match(lookup_value, range0, 1) is to return the value where look_value is in range
index(range1, match_return, col) is to return the reference where (match_return, col) is
cell("address", index_return) is to return the address of the lookup, like $C$4

in your example, it should be something like this: 

=Cell("address", Index(Sheet2!A1:X131, Match(lookup_value, sheet1!A1:A131,1), col)

Note:

  • col is the index of column to get the value which is correponding to the lookup_value in column A

  • set 3rd parm of Match depending on your needs

take a look at https://exceljet.net/formula/get-address-of-lookup-result for more details