CODEWITHSUNDEEP
excelvba
Alex Gordon
Alex Gordon

Reputation: 60912

recovering the cell address of a vlookup statement

is there a way to get address_of(vlookup(.....)) ??

where address_of = "A25" (or something in that format) ??

Upvotes: 7

Views: 48775

Answers (2)

Marc Thibault
Marc Thibault

Reputation: 1728

Use MATCH() rather than Lookup. The formula below assumes you've got a lookup table in A1:A12.

It looks for the value "60" and turns the returned row index into a textual range address (e.g. "$A$6").

=ADDRESS(MATCH(60,A1:A12,0),1)

The third parameter in MATCH() makes this an exact match.

Upvotes: 12

Dez
Dez

Reputation: 171

=ADDRESS(MATCH(60,A1:A12,0),1)

Missed out the ,0 making it approximate and not an exact match. This is not good for drop downs combo menus; if two things can be the same, then you need an exact match or it picks up the last one.

Upvotes: 17

Related Questions