Home |
Search |
Today's Posts |
#1
|
|||
|
|||
multiple vlookup
i have range(a1:j1000)
columns A,C,E,G,I are phone numbers columns B,D,F,H,J are serial numbers i need a vlookup function that lookups up the phone number then returns the serial number. my problem is that if the phone number being looked up is in column C it returns #N/A i am thinkin maybe a couple if functions, but im not sure thanks in advance |
#2
|
|||
|
|||
Hi
any chance to reorder your data in two columns. Everything else would make the formulas much more complicated -- Regards Frank Kabel Frankfurt, Germany choice wrote: i have range(a1:j1000) columns A,C,E,G,I are phone numbers columns B,D,F,H,J are serial numbers i need a vlookup function that lookups up the phone number then returns the serial number. my problem is that if the phone number being looked up is in column C it returns #N/A i am thinkin maybe a couple if functions, but im not sure thanks in advance |
#3
|
|||
|
|||
There may be a better way, but assuming that the first row contains your labels and your data starts in the second row, try the following... =INDEX(A2:J1000,SUMPRODUCT((A2:J1000=L2)*ROW(A2:J1 000))-CELL("row",A1),SUMPRODUCT((A2:J1000=L2)*COLUMN(A2: J1000))+1) ...where L2 contains the phone number of interest. Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=274273 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
Vlookup Syntax Error | New Users to Excel | |||
Can vlookup return multiple matches in a single cell? | Excel Discussion (Misc queries) | |||
Need help with modifying VLookUp | Excel Discussion (Misc queries) | |||
vlookup | Excel Worksheet Functions |