| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup
Can I have the row number returned by VLOOKUP when it finds the correct data?
-- Traa Dy Liooar Jock |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Jock
Array is A1 to B10 & lookup value in C1, Try:- =MATCH((VLOOKUP(C1,A1:B10,2,FALSE)),B1:B10,0) Mike "Jock" wrote: Can I have the row number returned by VLOOKUP when it finds the correct data? -- Traa Dy Liooar Jock |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
So far, so good.
It returns the first instance the criteria is matched rather than listing them if there's more than one. Also, could the lookup value be something like 5 so it returns all row numbers for values greater than, in this case, 5? -- Traa Dy Liooar Jock "Mike H" wrote: Jock Array is A1 to B10 & lookup value in C1, Try:- =MATCH((VLOOKUP(C1,A1:B10,2,FALSE)),B1:B10,0) Mike "Jock" wrote: Can I have the row number returned by VLOOKUP when it finds the correct data? -- Traa Dy Liooar Jock |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Hi Jock,
Put this in a cell and drag down. In the first cell it will return the row number of the first instance and in the second row the second etc. When it stops finding matches it will reutrn Ref errors. =MATCH(INDEX($A$1:$B$10,SMALL(IF($A$1:$B$10=$C$1,R OW($A$1:$B$10)-ROW($A$1)+1,ROW($B$10)+1),ROW(A1)),2),$B$1:$B$10,0 ) It's an array so Ctrl+Shift+Enter. Any more surprises:) Mike "Jock" wrote: So far, so good. It returns the first instance the criteria is matched rather than listing them if there's more than one. Also, could the lookup value be something like 5 so it returns all row numbers for values greater than, in this case, 5? -- Traa Dy Liooar Jock "Mike H" wrote: Jock Array is A1 to B10 & lookup value in C1, Try:- =MATCH((VLOOKUP(C1,A1:B10,2,FALSE)),B1:B10,0) Mike "Jock" wrote: Can I have the row number returned by VLOOKUP when it finds the correct data? -- Traa Dy Liooar Jock |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Thanks for this input, nearly there.....
If I put '5' B4, then C1 gives the correct row re, i.e. - 4. Another 5 in 'B9' gives the same row ref in C2. (that is, 4) A '5' in 'A4' puts "4" in C3 A '5' in 'A9' puts a "4" in C4 Removing the "5" from 'B9' changed C3 and C4 to '#N/A' and '#Ref!' respectively. hth -- Traa Dy Liooar Jock "Mike H" wrote: Hi Jock, Put this in a cell and drag down. In the first cell it will return the row number of the first instance and in the second row the second etc. When it stops finding matches it will reutrn Ref errors. =MATCH(INDEX($A$1:$B$10,SMALL(IF($A$1:$B$10=$C$1,R OW($A$1:$B$10)-ROW($A$1)+1,ROW($B$10)+1),ROW(A1)),2),$B$1:$B$10,0 ) It's an array so Ctrl+Shift+Enter. Any more surprises:) Mike "Jock" wrote: So far, so good. It returns the first instance the criteria is matched rather than listing them if there's more than one. Also, could the lookup value be something like 5 so it returns all row numbers for values greater than, in this case, 5? -- Traa Dy Liooar Jock "Mike H" wrote: Jock Array is A1 to B10 & lookup value in C1, Try:- =MATCH((VLOOKUP(C1,A1:B10,2,FALSE)),B1:B10,0) Mike "Jock" wrote: Can I have the row number returned by VLOOKUP when it finds the correct data? -- Traa Dy Liooar Jock |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Jock,
I'm confused, Can you confirm which column you want to lookup and return the row of, A or B Mike "Jock" wrote: Thanks for this input, nearly there..... If I put '5' B4, then C1 gives the correct row re, i.e. - 4. Another 5 in 'B9' gives the same row ref in C2. (that is, 4) A '5' in 'A4' puts "4" in C3 A '5' in 'A9' puts a "4" in C4 Removing the "5" from 'B9' changed C3 and C4 to '#N/A' and '#Ref!' respectively. hth -- Traa Dy Liooar Jock "Mike H" wrote: Hi Jock, Put this in a cell and drag down. In the first cell it will return the row number of the first instance and in the second row the second etc. When it stops finding matches it will reutrn Ref errors. =MATCH(INDEX($A$1:$B$10,SMALL(IF($A$1:$B$10=$C$1,R OW($A$1:$B$10)-ROW($A$1)+1,ROW($B$10)+1),ROW(A1)),2),$B$1:$B$10,0 ) It's an array so Ctrl+Shift+Enter. Any more surprises:) Mike "Jock" wrote: So far, so good. It returns the first instance the criteria is matched rather than listing them if there's more than one. Also, could the lookup value be something like 5 so it returns all row numbers for values greater than, in this case, 5? -- Traa Dy Liooar Jock "Mike H" wrote: Jock Array is A1 to B10 & lookup value in C1, Try:- =MATCH((VLOOKUP(C1,A1:B10,2,FALSE)),B1:B10,0) Mike "Jock" wrote: Can I have the row number returned by VLOOKUP when it finds the correct data? -- Traa Dy Liooar Jock |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Hybrid Mode
