Home |
Search |
Today's Posts |
#1
|
|||
|
|||
find Row
I currently use VLOOKUP to find the address for a company when their name is input. It returns the address but I would also like it to return the row it gets the details from, is this possible? thanks -- rocket0612 ------------------------------------------------------------------------ rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492 View this thread: http://www.excelforum.com/showthread...hreadid=476191 |
#2
|
|||
|
|||
MATCH function returns row number!
Regards, Stefi €˛rocket0612€¯ ezt Ć*rta: I currently use VLOOKUP to find the address for a company when their name is input. It returns the address but I would also like it to return the row it gets the details from, is this possible? thanks -- rocket0612 ------------------------------------------------------------------------ rocket0612's Profile: http://www.excelforum.com/member.php...o&userid=19492 View this thread: http://www.excelforum.com/showthread...hreadid=476191 |
#3
|
|||
|
|||
"Stefi" wrote in message ... MATCH function returns row number! No, it returns the index into the data. This could be the row number IF it is vertical data, but may not (MATCH("123",A100:A110,0 will never be between 100 and 110). |
#4
|
|||
|
|||
find Row
rocket0612 wrote: I currently use VLOOKUP to find the address for a company when their name is input. It returns the address but I would also like it to return the row it gets the details from, is this possible? thanks Given in A2:B4: x,7 y,6 z,9 the formula: =VLOOKUP("y",$A$2:$B$4,2,0) would return 6. =MATCH("y",$A$2:$A$4,0) would return the position of the lookup value "y", therefore also of the value associated with it. =MATCH("y",$A$2:$A$4,0)+ROW($A$2)-1 would return the native row number at which "y" is. =CELL("Address",INDEX($B$2:$B$4,MATCH("y",$A$2:$A$ 4,0))) would return the cell reference of the value associated with "y". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trapping a NO FIND after a find | Excel Discussion (Misc queries) | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
Find within Workbook. | Excel Discussion (Misc queries) | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |