Index Match

[Steven]

M N O
X P 1 4 7
Y Q 2 5 8
Y P 3 6 9

These are Cells A1 to E4

How do I return a lookup that Y, Q, O = 8

Thank you


Steven

 

[Peo Sjoblom]

One way

=INDEX(A1:E4,MATCH(1,(A1:A4="Y")*(B1:B4="Q"),0),MATCH("O",A1:E1,0))

entered with ctrl + shift & enter


of course you should replace all hard coded values like Y, Q and O with cell
references
that way you don't have to edit the formula itself when you change the
criteria

--


Regards,


Peo Sjoblom

 

[Teethless mama]

Try this:

=SUMPRODUCT((A2:A4="Y")*(B2:B4="Q")*(C1:E1="O"),C2:E4)

 

[ShaneDevenshire]

Hi,

Here are two other approaches:

=INDEX(E2:E4,MATCH("YQ",A2:A4&B2:B4,0))
and
=SUMPRODUCT(--(A2:A4&B2:B4="YQ"),E2:E4)

There is a major difference between these two formulas - the first one will
only retrieve the first occurance of Y in column A and Q in column B, whereas
the second one will find and sum all the entries in column E which meet the
two criteria.

If there will never be more than one row per condition then either formula
works.

To make it more flexible you can enter the YQ criteria in a cell and
reference that cell in both formulas.

 

[Mike B]

Hi,
I found your formula while searching for an answer to a similiar problem,
how would I write this to use the first two rows and first column rather than
the first two columns and first row?

Thanks
Mike B