Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple field match?
Hi!
I have a table where I want to match 4 different fields in 4 different columns and then reference the data to the right where these four fields match the search criteria. For example, if this was a 5 col x 4 row spreadsheet: 1 A 5 E xxxx1 1 A 5 E xxxx2 1 B 6 F xxxx3 1 B 6 F xxxx4 So if I want to match 1 B 6 F (each in it's own cell), it would return xxx3. Can anyone think of how to do this? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple field match?
In your example assume data is in columns A to E, starting row 1:
=INDEX(E1:E100,MATCH(1,(A1:A100)=1)*(B1:B100="B")* (C1:C100=6)*(D1:D100="F"),0),1) Enter with Ctrl+Shift+Enter (an array formula) HTH " wrote: Hi! I have a table where I want to match 4 different fields in 4 different columns and then reference the data to the right where these four fields match the search criteria. For example, if this was a 5 col x 4 row spreadsheet: 1 A 5 E xxxx1 1 A 5 E xxxx2 1 B 6 F xxxx3 1 B 6 F xxxx4 So if I want to match 1 B 6 F (each in it's own cell), it would return xxx3. Can anyone think of how to do this? Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple field match?
Hi!
One way: Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just ENTER): =INDEX(E1:E4,MATCH(1,(A1:A4=1)*(B1:B4="B")*(C1:C4= 6)*(D1:D4="F"),0)) Better to use cells to hold the criteria: H1 = 1 I1 = B J1 = 6 K1 = F =INDEX(E1:E4,MATCH(1,(A1:A4=H1)*(B1:B4=I1)*(C1:C4= J1)*(D1:D4=K1),0)) Also, since you have duplicate matching criteria: 1 B 6 F xxxx3 1 B 6 F xxxx4 The formula will ALWAYS return the corresponding value for the FIRST match. Biff wrote in message ps.com... Hi! I have a table where I want to match 4 different fields in 4 different columns and then reference the data to the right where these four fields match the search criteria. For example, if this was a 5 col x 4 row spreadsheet: 1 A 5 E xxxx1 1 A 5 E xxxx2 1 B 6 F xxxx3 1 B 6 F xxxx4 So if I want to match 1 B 6 F (each in it's own cell), it would return xxx3. Can anyone think of how to do this? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple field match?
How can you write this using VBA?
Thanks, Anna "Toppers" wrote: In your example assume data is in columns A to E, starting row 1: =INDEX(E1:E100,MATCH(1,(A1:A100)=1)*(B1:B100="B")* (C1:C100=6)*(D1:D100="F"),0),1) Enter with Ctrl+Shift+Enter (an array formula) HTH " wrote: Hi! I have a table where I want to match 4 different fields in 4 different columns and then reference the data to the right where these four fields match the search criteria. For example, if this was a 5 col x 4 row spreadsheet: 1 A 5 E xxxx1 1 A 5 E xxxx2 1 B 6 F xxxx3 1 B 6 F xxxx4 So if I want to match 1 B 6 F (each in it's own cell), it would return xxx3. Can anyone think of how to do this? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data in primay field changed however subsequent field does not upd | Excel Discussion (Misc queries) | |||
Match Single Numeric Criteria and Return Multiple Numeric Labels | Excel Worksheet Functions | |||
How do I filter with multiple conditions in one field? | Excel Discussion (Misc queries) | |||
Find a match that;s not exact | Excel Worksheet Functions | |||
HOW TO MATCH MULTIPLE COLUMNS WITH OR WITHOUT GAPS IN eXCEL ? | Excel Worksheet Functions |