Vlookup for multiple criteria

K

kieffer

I need for the formula to look at 4 criteria in column A, colum B,
column C and Column D. When the criteria in column A and B are
satisfied, it reports the value in column E.

Is Vlookup the right function?

Jim
 
B

Bob Phillips

=INDEX(E1:E10,MATCH(1,(A1:A10="Apples")*(B1:B10="Coyotes")*(C1:C10="Y")*(D1:
D10=1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
K

kieffer

I want to return a value in column E when column A,B,C and D ar
satisfied. I'm getting an error with the formula below

=INDEX(Table!E1:E10,MATCH(1,(Table!A5:A10=E7)*(Table!B5:B10=F7)*(Table!C5:C10=G7)*(Table!D1:D10=H7),0))


A B C D E
Poly PU5 Sola Max NTMc
Poly PU5 Gentex Natural NTMc
607 PC8 Essilor Natural Classic
Poly PU2 Gentex Blue Classic
*table is located in a tab labeled "Table
 
K

kieffer

I want to have a formula examine the contents in column A,B,C,D and i
satisified, it will return what is in column E



Table In Tab Named "Info"
A B C D E
Poly PU5 Gentex Comfort NTM
Triv PU5 Sola Max Class

In another tab, if someone inputs Poly, PU5, Gentex and Comfort, th
cell with a formula in it will return NTM.

The earlier post suggested using the Index formula followed wit
Ctr-Sht-Ent to force an array....however, the formula is giving me a
error
 
R

RagDyeR

Your formula has *unequal* range sizes!

Make them *all* the same size:

=INDEX(Table!E1:E10,MATCH(1,(Table!A1:A10=E7)*(Table!B1:B10=F7)*(Table!C1:C1
0=G7)*(Table!D1:D10=H7),0))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

message
I want to have a formula examine the contents in column A,B,C,D and if
satisified, it will return what is in column E



Table In Tab Named "Info"
A B C D E
Poly PU5 Gentex Comfort NTM
Triv PU5 Sola Max Class

In another tab, if someone inputs Poly, PU5, Gentex and Comfort, the
cell with a formula in it will return NTM.

The earlier post suggested using the Index formula followed with
Ctr-Sht-Ent to force an array....however, the formula is giving me an
error.
 
K

kieffer

=INDEX(Table!C4:F5,MATCH(1,(Table!C4:F5=C8)*(Table!C4:F5=D8)*(Table!C4:F5=E8)*(Table!C4:F5=F8),
0))

After I enter this formula and hit Ctr-Shf-Ent to force an array, I get
#n/a.

Does anyone see an issue with this formula?

Jim
 
K

kieffer

A B C D E
Poly PU5 Gentex Comfort NTM
Triv PU5 Sola Max Class Classic

What I'm looking for is a table in the "back" tab. This table would
contain a list of info as seen above.

The operator would enter in to the "front" tab for example,
Poly PU5 Gentex Comfort.....the formula, located in E, would return
NTM. Therefore, 4 criteria have to be met to return a value.

Jim
 
B

Bob Phillips

=INDEX(Back!E1:E20,MATCH(1,(Back!A1:A20=A1)*(Back!B1:B20=B1)*(Back!C1:C20=C1
)*(Back!D1:D20=D1),0))

still an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
A

anna

This is awesome, but here's a brain teaser....
I want the sheet in the formula to reference a cell! Meaning depending this
first criteria, it will match A1,B1,C1,and D1 to a different sheet depending
on the value in a cell I prescribe. Does that make sense? I guess another
wayto say it would be using the formula you provided below, I want "Back" to
be a cell reference. Could you help? Thanks, Anna.

Bob Phillips said:
=INDEX(Back!E1:E20,MATCH(1,(Back!A1:A20=A1)*(Back!B1:B20=B1)*(Back!C1:C20=C1
)*(Back!D1:D20=D1),0))

still an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
G

gfactor

ragdye...related quesiton.

i have a table that has the following data:

a b c d
1 y1 p1 2 5
2 x1 p2 1 1
3 y1 p3 4 0
4 z1 p4 4 3
5 q1 p5 3 4

I am using the following formula:
=INDEX('PO Detail'!C$2:C$188,SMALL(IF('PO Detail'!A$2:A$188=$E$4,ROW('PO
Detail'!A$2:A$188)),ROW('PO Detail'!A2)))

....to get each instance of "y1" and the correxponding value in column "b".
but i want to expand the formula so that it only returns the result of
looking up "y1" if the value in column d is "0".

any thoughts?

g
 
Top