integrating vlookup and cell functions

**AMT** · 11-28-2007, 03:57 PM

I'm trying to find a way to return the location of the results of a Vlookup. To try to simplify this, I want a formula to do a Vlookup and for another formula to be able to report what cell number the Vlookup is referring to when it finds what it is looking for. Does anyone have any suggestions?

**NBVC** · 11-28-2007, 04:03 PM

the Match() function will give you the vertical position within the range that a match is found.

e.g. =Match(A1,X1:X100,0) where A1 is the look up value (same as your Vlookup) and X1:X100 is the column range to find the look up value (the first column of your Vlookup table).

**AMT** · 11-29-2007, 10:41 AM

Thanks for the tip, as I've never heard of the "match" function before, but is there a way to use it to return the cell location (e.g. a1, c3, etc.) instead of the cell position?

Originally Posted by NBVC

the Match() function will give you the vertical position within the range that a match is found.

e.g. =Match(A1,X1:X100,0) where A1 is the look up value (same as your Vlookup) and X1:X100 is the column range to find the look up value (the first column of your Vlookup table).

**NBVC** · 11-29-2007, 10:47 AM

If you're using Vlookup...you probably know the column to pull from...so you can probably use Address function....

e.g. =Address(Match(A1,X1:X100,0),3)

where 3 is the column number (starting count from column A) where the Vlookup pulled value would be coming from...

Note: The range within the match function must start at row 1 (or you can add the number of rows you offset by at the end of the Match function---eg. =Match(A1,X5:X100,0)+4

Thread: integrating vlookup and cell functions

Thread Tools

Display

integrating vlookup and cell functions

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions