Vlookup and Trace Return Cell

[TM]

Hi,

Wondering how to have excel return the cell that the vlookup equation is
drawing from? For example, when my vlookup returns the value "45" within a
given table, how do I trace where that exact cell is that contains the 45?
When I try the formulas -> trace precedents, the vlookup only shows the
entire table that the function is using and not the cell of the value (the
45) it's returning.

Further, I should elaborate on my end goal and perhaps there is another,
better way: I have two tabs with identical set of values, but in different
orders (which is how they should be). Nevertheless, since all the values are
the same, I want to have 1 tab reference to another tab's value. Ideally the
tables would be identical and I could say set tab 1's Cell A1 = 'Tab 2'!A1;
however, as I mentioned they are in different orders. Therefore, I would
have to manually look for each one! So my work around is to have vlookup
search for the identical value from one table in another, and now, per my
question above, return the cell of which it's gathering from.

Hope that makes sense, thanks for the help!
TM

 

[Martin Fishlock]

Hi TM:

IFAIK there is no way to get the cell reference from the vlookup function.

You can try and use the match function to get the index number and then use
the index function get the value.

 

[PJ]

Hi TM

If I understand right, you have two identical lists but in different orders,
one in Sheet 1 and the other in Sheet 2, and you want to find the cell in
Sheet 2 containing the value of a given cell in Sheet 1.

With the lists in column A in both sheets and the value you want to match in
say A3, try this formula:

="Sheet2!A"&MATCH(A3,Sheet2!A1:A12,0)