Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
continue VLOOKUP to next value
I'm using VLOOKUP function to search through col A to extract data from
col B within the same row. However, there are multiple data in col A, which fulfil the condition defined in VLOOKUP. The function finds only the first cell that fulfills the condition. Is there any way how to repeat/continue VLOOKUP search for next matching value bellow in col A and thus place several extracted values from col B to new a column? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
continue VLOOKUP to next value
Here's a play using non-array formulas ..
Assume source data in cols A and B, row2 down to row100 Input the lookup value in C1 Put in C2: =IF(ISERROR(SMALL(D:D,ROW()-1)),"",INDEX(B:B,MATCH(SMALL(D:D,ROW()-1),D:D,0))) Put in D2: =IF(A2="","",IF(A2=$C$1,ROW(),"")) (Leave D1 empty) Select C2:D2, fill down to D100 (cover the extent of the source data) Col C will return the results from col B for the lookup value input in C1, with all results neatly bunched at the top (You could also use data filter autofilter on col A) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "rich" wrote: I'm using VLOOKUP function to search through col A to extract data from col B within the same row. However, there are multiple data in col A, which fulfil the condition defined in VLOOKUP. The function finds only the first cell that fulfills the condition. Is there any way how to repeat/continue VLOOKUP search for next matching value bellow in col A and thus place several extracted values from col B to new a column? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Problem (limitation)? | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |