Multiple field match?

[robotman@gmail.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.

[Toppers]

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

"robotman@gmail.com" 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.
>
>

[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)*(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

<robotman@gmail.com> wrote in message
news:1156280087.354951.10230@74g2000cwt.googlegroups.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.
>