| 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Not as confused as me, I'll bet.
Column B please. -- Traa Dy Liooar Jock "Mike H" wrote: 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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Jock,
After having failed with Index/match and a UDF, a completely different approach using a helper column. Col a Col B Col C Col D a a1 12 a b b1 13 c c1 14 a a2 15 e e1 16 a a3 17 e e2 18 b b2 19 a a4 20 b b3 21 Column B1 has a formula =A1&COUNTIF($A$1:A1,A1) Put this in and drag down. You can hide this helper column. In another cell enter =MATCH(VLOOKUP($D$1&ROW(),$B$1:$C$10,2,FALSE),$C$1 :$C$10,0) This matches the contents of D1 with Column B, returns column C and the row of that. Drag down for the second and subsequent instances of whaever is in D1. What the *** does Traa Dy Liooar mean? Mike "Jock" wrote: Not as confused as me, I'll bet. Column B please. -- Traa Dy Liooar Jock "Mike H" wrote: 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Hi Mike,
It's a Manx (Isle Of Man) Gaelic term which translates as "Time Eough" or "Time Galour"..according to google.. "Mike H" wrote: Jock, After having failed with Index/match and a UDF, a completely different approach using a helper column. Col a Col B Col C Col D a a1 12 a b b1 13 c c1 14 a a2 15 e e1 16 a a3 17 e e2 18 b b2 19 a a4 20 b b3 21 Column B1 has a formula =A1&COUNTIF($A$1:A1,A1) Put this in and drag down. You can hide this helper column. In another cell enter =MATCH(VLOOKUP($D$1&ROW(),$B$1:$C$10,2,FALSE),$C$1 :$C$10,0) This matches the contents of D1 with Column B, returns column C and the row of that. Drag down for the second and subsequent instances of whaever is in D1. What the *** does Traa Dy Liooar mean? Mike "Jock" wrote: Not as confused as me, I'll bet. Column B please. -- Traa Dy Liooar Jock "Mike H" wrote: 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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
vlookup
Thanks Mike, that's got it!
Traa dy liooar is Manx (Gaelic) for 'Time Enough' - nothing happens in a hurry here!! -- Traa Dy Liooar Jock "Mike H" wrote: Jock, After having failed with Index/match and a UDF, a completely different approach using a helper column. Col a Col B Col C Col D a a1 12 a b b1 13 c c1 14 a a2 15 e e1 16 a a3 17 e e2 18 b b2 19 a a4 20 b b3 21 Column B1 has a formula =A1&COUNTIF($A$1:A1,A1) Put this in and drag down. You can hide this helper column. In another cell enter =MATCH(VLOOKUP($D$1&ROW(),$B$1:$C$10,2,FALSE),$C$1 :$C$10,0) This matches the contents of D1 with Column B, returns column C and the row of that. Drag down for the second and subsequent instances of whaever is in D1. What the *** does Traa Dy Liooar mean? Mike "Jock" wrote: Not as confused as me, I'll bet. Column B please. -- Traa Dy Liooar Jock "Mike H" wrote: 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 | |||
Linear Mode
