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 |
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 |
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 |
All times are GMT +1. The time now is 02:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com