Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
I have the following situation:
I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
Paul, you can use a sumproduct funcion
=sumproduct(--(a2:a500=1)*(b2:b500=2)*(c2:c3=3),(d2:d500)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Paul" escreveu: I have the following situation: I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
Hi,
You didn't tell us where name is is this assumes column D. Try this =INDEX($D$2:$D$12,MATCH(1,INDEX(($A$2:$A$12=K1)*($ B$2:$B$12=L1)*($C$2:$C$12=M1),),),) Where K1, L1 & M1 are the values you are lookung for in Columns A, B, & C Mike "Paul" wrote: I have the following situation: I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
Thanks for the quick response, I think you might be better able to assist if
you had some real data A B C D E GMPName LongName Area SOVID Name GMP 1 Blue General Items 100 General Conditions GMP 1 Green General Items 101 Clearing & Grubbing GMP 1 Red General Items 102 Dust Control GMP 1 Orange General Items 103 Mobilization Knowing the GMPName, LongName, Area, and SOVID, I want to return the Name field. It is a unique value. I dont have much experience with Match & Index, but I dont see how the proposed use of a 'Sumproduct' function would work? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
Marcelo,
Sumproduct by design multiplies arrays which isn't much use when looking for a text value. You'd have to be a bit more creative to achieve the desired result with sumproduct. Try this OFFSET(D1, SUMPRODUCT( (A2:A10=K1)*(B2:B10=L1)*(C2:C10=M1), ROW(D2:D10)-1 ),0 ) Mike "Marcelo" wrote: Paul, you can use a sumproduct funcion =sumproduct(--(a2:a500=1)*(b2:b500=2)*(c2:c3=3),(d2:d500)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Paul" escreveu: I have the following situation: I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
Mike,
I put this in, and I have no idea of how it works but it does. I consider myself to be a pretty strong user, but I have no idea of how this works. Would it be too much to ask for a narrative of what this is doing? "Mike H" wrote: Hi, You didn't tell us where name is is this assumes column D. Try this =INDEX($D$2:$D$12,MATCH(1,INDEX(($A$2:$A$12=K1)*($ B$2:$B$12=L1)*($C$2:$C$12=M1),),),) Where K1, L1 & M1 are the values you are lookung for in Columns A, B, & C Mike "Paul" wrote: I have the following situation: I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
Paul
The easiest way to understand it to break it down and the first bit is easy index(d2:d12 is what we are looking for we then match(1,----- i.e the number 1 (or true) in each of the columns by comparing the lookup value to the column and if a full row of 1's (Or TRUE's) is returmed we have a solution Select the formula and then tools|formula auditing and evaluate formula and you'll see exactly how it works You can do the same thing with offset and sumproduct =OFFSET(D1, SUMPRODUCT( (A2:A10=K1)*(B2:B10=L1)*(C2:C10=M1), ROW(D2:D10)-1 ),0 ) Mike "Paul" wrote: Mike, I put this in, and I have no idea of how it works but it does. I consider myself to be a pretty strong user, but I have no idea of how this works. Would it be too much to ask for a narrative of what this is doing? "Mike H" wrote: Hi, You didn't tell us where name is is this assumes column D. Try this =INDEX($D$2:$D$12,MATCH(1,INDEX(($A$2:$A$12=K1)*($ B$2:$B$12=L1)*($C$2:$C$12=M1),),),) Where K1, L1 & M1 are the values you are lookung for in Columns A, B, & C Mike "Paul" wrote: I have the following situation: I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
thx
-- regards from Brazil Thanks in advance for your feedback. Marcelo "Mike H" escreveu: Marcelo, Sumproduct by design multiplies arrays which isn't much use when looking for a text value. You'd have to be a bit more creative to achieve the desired result with sumproduct. Try this OFFSET(D1, SUMPRODUCT( (A2:A10=K1)*(B2:B10=L1)*(C2:C10=M1), ROW(D2:D10)-1 ),0 ) Mike "Marcelo" wrote: Paul, you can use a sumproduct funcion =sumproduct(--(a2:a500=1)*(b2:b500=2)*(c2:c3=3),(d2:d500)) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Paul" escreveu: I have the following situation: I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup on Multiple Criteria
Why the double unary minus, Marcelo?
If you are wanting an arithmetic operation to coerce the boolean to a number, won't the multiplication do it anyway? And are you sure that the formula will work with the unequal ranges? Oughn't the C3 to be C500? -- David Biddulph Marcelo wrote: Paul, you can use a sumproduct funcion =sumproduct(--(a2:a500=1)*(b2:b500=2)*(c2:c3=3),(d2:d500)) hth I have the following situation: I have a sheet that I need to lookup "Name" when "Col A" = value 1 AND "Col B" = value 2, and "Col 3" = value 3. There is only one unique solution. Basically I need a Vlookup with an "AND" function. The value that I am looking for a text field, or else I would try a pivot table. Help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup by multiple criteria. | Excel Worksheet Functions | |||
Multiple lookup criteria | Excel Discussion (Misc queries) | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
Lookup with Multiple Criteria | Excel Discussion (Misc queries) |