|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
January 6th, 2010, 03:38 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I lookup when match has more than one value?
Hi,
I'm doing a Vlookup match function and it works, but what I want to do is get the second match of a value. eg: ..............1st.....2nd......3rd........4th..... .. meat....good.....bad.....ok..........good.... chicken.OK......good......bad.......ok..... fish......good....bad......bad.......ok...... meat....OK....good...good.......good.... B12=meat B13= 2nd my formula looks something like this: VLOOKUP(B12,A1:AF10, MATCH(B13,B1:AF1,0)+1 ,FALSE) what it does is get me the first row meat, 2nd column (bad) but what I want is second row meat, 2nd column (good). how do I ammend the formula accordingly. Thanks! 
|
|
#2
January 6th, 2010, 04:40 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I lookup when match has more than one value?
If the lookup value appears more than once in the table how do you determine
whether you want the first instance or the nth instance? Are there duplicates for all of the lookup values? Are there only 2 instances of the lookup value? -- Biff Microsoft Excel MVP "Paulynn" wrote in message ... Hi, I'm doing a Vlookup match function and it works, but what I want to do is get the second match of a value. eg: .............1st.....2nd......3rd........4th...... . meat....good.....bad.....ok..........good.... chicken.OK......good......bad.......ok..... fish......good....bad......bad.......ok...... meat....OK....good...good.......good.... B12=meat B13= 2nd my formula looks something like this: VLOOKUP(B12,A1:AF10, MATCH(B13,B1:AF1,0)+1 ,FALSE) what it does is get me the first row meat, 2nd column (bad) but what I want is second row meat, 2nd column (good). how do I ammend the formula accordingly. Thanks!
|
|
#3
January 6th, 2010, 04:54 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I lookup when match has more than one value?
Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple results. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=formula}" B12=meat B13= 2nd =INDEX(B1:E10,SMALL(IF(A1:A10=B12,ROW(A1:A10)),2), MATCH(B13,B1:AF1,0)) Or with 2 in a cell reference say B14 =INDEX(B1:E10,SMALL(IF(A1:A10=B12,ROW(A1:A10)),B14 ),MATCH(B13,B1:AF1,0)) -- Jacob "Paulynn" wrote: Hi, I'm doing a Vlookup match function and it works, but what I want to do is get the second match of a value. eg: .............1st.....2nd......3rd........4th...... . meat....good.....bad.....ok..........good.... chicken.OK......good......bad.......ok..... fish......good....bad......bad.......ok...... meat....OK....good...good.......good.... B12=meat B13= 2nd my formula looks something like this: VLOOKUP(B12,A1:AF10, MATCH(B13,B1:AF1,0)+1 ,FALSE) what it does is get me the first row meat, 2nd column (bad) but what I want is second row meat, 2nd column (good). how do I ammend the formula accordingly. Thanks!
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
