Multiple field match?

[robotman]

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.

 

[Guest]

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)*(D1100="F"),0),1)

Enter with Ctrl+Shift+Enter (an array formula)

HTH

 

[Biff]

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)*(D14="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)*(D14=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

 

[Guest]

How can you write this using VBA?

Thanks,
Anna

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)*(D1100="F"),0),1)

Enter with Ctrl+Shift+Enter (an array formula)

HTH