Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Multi-condition vlookup
I've seen several posts for multi-condition vlookups when data is in
sequential columns but it wasn't clear how to accomplish the same thing when you need to index a column not adjacent to the columns you are testing. For example using the following: Year Model Company a b c e 2005 Red a 10 2005 Green b 100 2004 Green c 50 2005 Red a 20 2002 Yellow a 10 How would you right a vlookup that only searched for 2005, Red, Company A and returned 20 from colum e? Thanks in advance. |
#2
|
|||
|
|||
Hi!
Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0)) You might also be able to use the non-array entered Sumproduct function since the returned value is numeric: =SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100) Biff "Hobbeson" wrote in message ... I've seen several posts for multi-condition vlookups when data is in sequential columns but it wasn't clear how to accomplish the same thing when you need to index a column not adjacent to the columns you are testing. For example using the following: Year Model Company a b c e 2005 Red a 10 2005 Green b 100 2004 Green c 50 2005 Red a 20 2002 Yellow a 10 How would you right a vlookup that only searched for 2005, Red, Company A and returned 20 from colum e? Thanks in advance. |
#3
|
|||
|
|||
Oops. A lost comma!
=INDEX(D1:D100,MATCH(1,(A1:A100=2005)*(B1:B100="Re d")*(C1:C100="A"),0)) The OP may want to consider using a pivottable, too. To read more about the pivottable stuff, you may want to look at some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx Biff wrote: Hi! Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0)) You might also be able to use the non-array entered Sumproduct function since the returned value is numeric: =SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100) Biff "Hobbeson" wrote in message ... I've seen several posts for multi-condition vlookups when data is in sequential columns but it wasn't clear how to accomplish the same thing when you need to index a column not adjacent to the columns you are testing. For example using the following: Year Model Company a b c e 2005 Red a 10 2005 Green b 100 2004 Green c 50 2005 Red a 20 2002 Yellow a 10 How would you right a vlookup that only searched for 2005, Red, Company A and returned 20 from colum e? Thanks in advance. -- Dave Peterson |
#4
|
|||
|
|||
Ooops!
I see there are dupe criteria that equal 2005 "Red" "A".....so: How do you determine which instance of matching criteria you want returned? This array formula will return the LAST instance: =INDEX(D1:D100,LARGE((A1:A100=2005)*(B1:B100="Red" )*(C1:C100="a")*(ROW(A1:A100)),1)) Also, since there are duplicate matching criteria, the Sumproduct will not work. I need to slow down and read more carefully! Biff "Biff" wrote in message ... Hi! Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0)) You might also be able to use the non-array entered Sumproduct function since the returned value is numeric: =SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100) Biff "Hobbeson" wrote in message ... I've seen several posts for multi-condition vlookups when data is in sequential columns but it wasn't clear how to accomplish the same thing when you need to index a column not adjacent to the columns you are testing. For example using the following: Year Model Company a b c e 2005 Red a 10 2005 Green b 100 2004 Green c 50 2005 Red a 20 2002 Yellow a 10 How would you right a vlookup that only searched for 2005, Red, Company A and returned 20 from colum e? Thanks in advance. |
#5
|
|||
|
|||
Maybe the OP had a typo, and he wants 30 instead of 20.<g
Then ... *only* the Sumproduct would suffice. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Ooops! I see there are dupe criteria that equal 2005 "Red" "A".....so: How do you determine which instance of matching criteria you want returned? This array formula will return the LAST instance: =INDEX(D1:D100,LARGE((A1:A100=2005)*(B1:B100="Red" )*(C1:C100="a")*(ROW(A1:A1 00)),1)) Also, since there are duplicate matching criteria, the Sumproduct will not work. I need to slow down and read more carefully! Biff "Biff" wrote in message ... Hi! Entered as an array using the key combo of CTRL,SHIFT,ENTER: =INDEX(D1:D100,MATCH(1(A1:A100=2005)*(B1:B100="Red ")*(C1:C100="A"),0)) You might also be able to use the non-array entered Sumproduct function since the returned value is numeric: =SUMPRODUCT(--(A1:A100=2005),--(B1:B100="Red"),--(C1:C100="A"),D1:D100) Biff "Hobbeson" wrote in message ... I've seen several posts for multi-condition vlookups when data is in sequential columns but it wasn't clear how to accomplish the same thing when you need to index a column not adjacent to the columns you are testing. For example using the following: Year Model Company a b c e 2005 Red a 10 2005 Green b 100 2004 Green c 50 2005 Red a 20 2002 Yellow a 10 How would you right a vlookup that only searched for 2005, Red, Company A and returned 20 from colum e? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Question. | Excel Discussion (Misc queries) | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup in two condition | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |