| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup with two criteria | Excel Worksheet Functions | |||
| vlookup with two criteria | Excel Worksheet Functions | |||
| Vlookup with multiple criteria | Excel Worksheet Functions | |||
| VLookup on two criteria - Not two dimensional | Excel Worksheet Functions | |||
| SUMIF using VLOOKUP as criteria | Excel Worksheet Functions | |||
Linear Mode
