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.

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.

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.

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

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.

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.

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.

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.

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.