Vlookup based on 2 conditions

[bat18]

Hi,

I am looking to do a vlookup based on 2 conditions and have no idea how to do it.

At the moment I am doing a vlookup on a cell in column A and it is producing a particular value, the problem is that this cell appears a few times in column A. So what I need to do is lookup the cell in column A but also based on the corresponding data in column B to get the correct lookup value.

For example

2109 Dave
2109 John
2108 Dave

So at the moment when I am looking up 2109 it is always producing the value corresponding to what Dave has put in, I need to know what both Dave and John have put in

I hope this made some sense, any help would be truly appreciated.

Thanks

 

[MrKowz]

This requires an INDEX/MATCH formula:

Excel Workbook
	A	B	C	D	E	F
1	2109	Dave	Apple		Number to Lookup	2109
2	2109	John	Orange		Name to Lookup	John
3	2108	Dave	Orange		Value Returned	Orange
Sheet1

Excel 2003

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[jasonb75]

MrKowz, something you might like

{=INDEX(C1:C3,MATCH(1,--IF(A1:A3=F1,B1:B3=F2),0))}

 

[MrKowz]

MrKowz, something you might like

{=INDEX(C1:C3,MATCH(1,--IF(A1:A3=F1,B1:B3=F2),0))}

Good find for when there is only two conditions. However, a problem would arise if there is three or more.

 

[bat18]

Thanks for the help, but I'm not getting any luck with it at the minute. This may be because I am looking up values from a different spreadsheet??

My formula looks something like this

=INDEX('[ACR P9, 2010.xls]Summary ACR'!$V7:$V500, MATCH(1, IF('[ACR P9, 2010.xls]Summary ACR'!$A7:$A500=A23, IF('[ACR P9, 2010.xls]Summary ACR'!$E7:$E500=E23, 1)), 0))

I am trying to find a value in column V of a different worksheet based on the criteria in columns A and E of the worksheet I'm in now.

The values in columns E I have obtained using a vlookup from the other spreadsheet, would this cause problems

Thanks again for the help

 

[jasonb75]

You weren't meant to notice that

{=INDEX(D1:D3,MATCH(1,--IF(A1:A3=F1,IF(B1:B3=F2,C1:C3=F3)),0))}

I've always used sumproduct and small to do this task, but this method looks more efficient

 

[MrKowz]

Did you confirm formula entry with CTRL+SHIFT+ENTER? Your formula looks fine.

 

[bat18]

No I did not and now it works prefectly. I owe you my largest THANKYOU of the week!!