Working out which cell Vlookup refers to

Question

Hi, say if I see this Vlookup formula in my Excel spreadsheet:

=VLOOKUP(G37,'tb 2008'!$H$142:$M$247,6,FALSE)

Then is there an efficient way to find out which cell it is referencing? I don't want to manually perform the Vlookup in my head to find out which cell it found that fits the parameters.

Answer 1

I'm not sure about a nice way to do it, but you can just fill a column (for example, column N) with row numbers and then use two VLOOKUPs to get you the value from column 6 as well as the row number from column 7. Then you have the cell, it's M# where # is whatever is returned by the second VLOOKUP.

Answer 2

No, there is no built in way of finding the cell.

Answer 3

By Which cell if you mean the Address of the Cell, you can use Index/Match and Index can be made to return a reference instead of the value

Answer 4

=ADDRESS( ROW((INDEX('tb2008'!$H$142:$H$247,MATCH(G37,'tb2008'!$L$142:$L$247,0)))), COLUMN((INDEX('tb2008'!$H$142:$H$247,MATCH(G37,'tb2008'!$L$142:$L$247,0)))) )

match returns the number of an element in an array, eg (assume the range A1:A5 is filled with A,B,C,D,E: match(C,A1:A5,0) = 3

index will return a reference to a cell based on an row and column number. index(A1:A5, 0, 3) = C

and then we use the ROW and COLUMN functions to get the row and column address of that reference. then put that together using ADDRESS