Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - more than one return required
My range of data (table array) has more than one row that meet the VLOOKUP
criteria I have set. I have a VLOOKUP in eg A1 =VLOOKUP($K$1,Order_Log,6,FALSE). In this I want the first value to be returned (as VLOOKUP does per standard, using FALSE). This I can do. In cell A2 however, I would like to return the 2nd value from my range of data. In cell A3, the 3rd ....and so on (only up until around the 6th value). What do I need to add into my A1 formula, for the 2nd value to be returned in A2? I guessed it may be as easy as FALSE +1, but this does not seem to work! Many thanks Jenny |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - more than one return required
One way, let's say the column that contains your keys is in L1:L10, the
column with the data you want returned is in M1:M10, and K1 contains the value you want to look up: IF(ROW()COUNTIF($L$1:$L$10,$K$1),"",INDEX($M$1:$M $10,SMALL(IF($L$1:$L$10=$K$1,ROW(INDIRECT("1:"&ROW S($L$1:$L$10))),""),ROW()))) The item that will be returned will be dependent on the row in which the formula is entered (if entered in cell C8, it will return the 8th item). You could use a cell reference that specifies which item to return instead of Row(), then you're not locked into having to enter the formula in a specific row. "luvthavodka" wrote: My range of data (table array) has more than one row that meet the VLOOKUP criteria I have set. I have a VLOOKUP in eg A1 =VLOOKUP($K$1,Order_Log,6,FALSE). In this I want the first value to be returned (as VLOOKUP does per standard, using FALSE). This I can do. In cell A2 however, I would like to return the 2nd value from my range of data. In cell A3, the 3rd ....and so on (only up until around the 6th value). What do I need to add into my A1 formula, for the 2nd value to be returned in A2? I guessed it may be as easy as FALSE +1, but this does not seem to work! Many thanks Jenny |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - more than one return required
Thanks JMB,
Could you give me an example of say, the cell I'm entering this formula into is C8, but I actually require the 2nd item to be returned? Many thanks Jenny "JMB" wrote: One way, let's say the column that contains your keys is in L1:L10, the column with the data you want returned is in M1:M10, and K1 contains the value you want to look up: IF(ROW()COUNTIF($L$1:$L$10,$K$1),"",INDEX($M$1:$M $10,SMALL(IF($L$1:$L$10=$K$1,ROW(INDIRECT("1:"&ROW S($L$1:$L$10))),""),ROW()))) The item that will be returned will be dependent on the row in which the formula is entered (if entered in cell C8, it will return the 8th item). You could use a cell reference that specifies which item to return instead of Row(), then you're not locked into having to enter the formula in a specific row. "luvthavodka" wrote: My range of data (table array) has more than one row that meet the VLOOKUP criteria I have set. I have a VLOOKUP in eg A1 =VLOOKUP($K$1,Order_Log,6,FALSE). In this I want the first value to be returned (as VLOOKUP does per standard, using FALSE). This I can do. In cell A2 however, I would like to return the 2nd value from my range of data. In cell A3, the 3rd ....and so on (only up until around the 6th value). What do I need to add into my A1 formula, for the 2nd value to be returned in A2? I guessed it may be as easy as FALSE +1, but this does not seem to work! Many thanks Jenny |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP - more than one return required
Enter 2 in another cell (say B8) and change Row() to B8, so then the formula
becomes: =IF(B8COUNTIF($L$1:$L$10,$K$1),"",INDEX($M$1:$M$1 0,SMALL(IF($L$1:$L$10=$K$1,ROW(INDIRECT("1:"&ROWS( $L$1:$L$10))),""),B8))) You could hardcode the 2 into the formula, but it is easier to work with when using an external cell reference to contain your criteria. Also, I don't remember if I stated earlier that this is an array formula. After keying it into the formula bar, you must hit Control+Shift+Enter. "luvthavodka" wrote: Thanks JMB, Could you give me an example of say, the cell I'm entering this formula into is C8, but I actually require the 2nd item to be returned? Many thanks Jenny "JMB" wrote: One way, let's say the column that contains your keys is in L1:L10, the column with the data you want returned is in M1:M10, and K1 contains the value you want to look up: IF(ROW()COUNTIF($L$1:$L$10,$K$1),"",INDEX($M$1:$M $10,SMALL(IF($L$1:$L$10=$K$1,ROW(INDIRECT("1:"&ROW S($L$1:$L$10))),""),ROW()))) The item that will be returned will be dependent on the row in which the formula is entered (if entered in cell C8, it will return the 8th item). You could use a cell reference that specifies which item to return instead of Row(), then you're not locked into having to enter the formula in a specific row. "luvthavodka" wrote: My range of data (table array) has more than one row that meet the VLOOKUP criteria I have set. I have a VLOOKUP in eg A1 =VLOOKUP($K$1,Order_Log,6,FALSE). In this I want the first value to be returned (as VLOOKUP does per standard, using FALSE). This I can do. In cell A2 however, I would like to return the 2nd value from my range of data. In cell A3, the 3rd ....and so on (only up until around the 6th value). What do I need to add into my A1 formula, for the 2nd value to be returned in A2? I guessed it may be as easy as FALSE +1, but this does not seem to work! Many thanks Jenny |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup function return all values | Excel Worksheet Functions | |||
Excel 2000 VLOOKUP returns #N/A unless press F2 return on source? | Excel Worksheet Functions | |||
Using a Vlookup to return values in a data list? | Excel Worksheet Functions | |||
Want VLookup to Return the row above | Excel Worksheet Functions | |||
Vlookup of an if statement return | Excel Worksheet Functions |