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.

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.

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.

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.