Index Match
 

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

Index Match
 

One way

=INDEX(A1:E4,MATCH(1,(A1:A4="Y")*(B1:B4="Q"),0),MA TCH("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

"Steven" wrote in message
...
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

Index Match
 

Try this:

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



"Steven" wrote:

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

Index Match
 

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.
--
Thanks,
Shane Devenshire


"Steven" wrote:

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

Index Match
 

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


"Peo Sjoblom" wrote:

One way

=INDEX(A1:E4,MATCH(1,(A1:A4="Y")*(B1:B4="Q"),0),MA TCH("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

"Steven" wrote in message
...
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