How to return the value of a cell based on two colums match criter

Y

yimmin

Arrrggghhh

I'm tearing my hair out with this one.

I need to create a formula that will look up the data from 2 seperate cells
in 2 colums and return the corresponding value in the 3rd colum.

Can anyone help?
 
N

Niek Otten

Insert a helper column in which you cancatenate the two keys. Concatenate
the search arguments too.
 
M

Mike H

Hi,

This assumes the criteria are in columns A & B and you want to return column C
The lookup values are in D1 & E1

=INDEX(C1:C20,MATCH(1,(A1:A20=D1)*(B1:B20=E1),0))

'This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.


MIke
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Index / Match / value in a range? 2
Index and Match 5
Return BLANK is cell value sought is blank 13
Two colums match return text from another 12
How do I return the value of an adjacent cell? 2
How to divide sheet in two or more sets of colums 3
Return value with 2 conditions (using Lookup/Match/Index) 3
Excel returning more than one value from a lookup table that match the same value 3

Top