Vlookup for multiple criteria
 

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


--
kieffer
------------------------------------------------------------------------
kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

=INDEX(E1:E10,MATCH(1,(A1:A10="Apples")*(B1:B10="C oyotes")*(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)

"kieffer" wrote in
message ...

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


--
kieffer
------------------------------------------------------------------------
kieffer's Profile:
http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

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

=INDEX(Table!E1:E10,MATCH(1,(Table!A5:A10=E7)*(Tab le!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"


--
kieffer
------------------------------------------------------------------------
kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

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.


--
kieffer
------------------------------------------------------------------------
kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

Shouldn't A5, B5, C5 all be in Row 1 (A1, B1, C1)???

kieffer wrote:

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

=INDEX(Table!E1:E10,MATCH(1,(Table!A5:A10=E7)*(Tab le!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"

--
kieffer
------------------------------------------------------------------------
kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

--

Dave Peterson

Vlookup for multiple criteria
 

Try it one more time with the ranges all the same size.

kieffer wrote:

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.

--
kieffer
------------------------------------------------------------------------
kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

--

Dave Peterson

Vlookup for multiple criteria
 

Your formula has *unequal* range sizes!

Make them *all* the same size:

=INDEX(Table!E1:E10,MATCH(1,(Table!A1:A10=E7)*(Tab le!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!
================================================== ===

"kieffer" wrote in
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.


--
kieffer
------------------------------------------------------------------------
kieffer's Profile:
http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

=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


--
kieffer
------------------------------------------------------------------------
kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

Can you provide a sample of your data, along with the expected results?

In article ,
kieffer wrote:

=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

Vlookup for multiple criteria
 

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


--
kieffer
------------------------------------------------------------------------
kieffer's Profile: http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

=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)

"kieffer" wrote in
message ...

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


--
kieffer
------------------------------------------------------------------------
kieffer's Profile:
http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

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" wrote:

=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)

"kieffer" wrote in
message ...

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


--
kieffer
------------------------------------------------------------------------
kieffer's Profile:
http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671

Vlookup for multiple criteria
 

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




"RagDyeR" wrote:

Your formula has *unequal* range sizes!

Make them *all* the same size:

=INDEX(Table!E1:E10,MATCH(1,(Table!A1:A10=E7)*(Tab le!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!
================================================== ===

"kieffer" wrote in
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.


--
kieffer
------------------------------------------------------------------------
kieffer's Profile:
http://www.excelforum.com/member.php...o&userid=30848
View this thread: http://www.excelforum.com/showthread...hreadid=551671