Lookup Function Based on 2 Cells in the Same Row

[turner38]

Hello All,

I am trying to find a way to verify that my worksheet contains a certain line of data.

Below is my worksheet:

Excel Workbook
	A	B	C
1	Year	Week	Place
2	2011	22	Florida
3	2011	22	Florida
4	2011	23	Florida
5	2011	23	Florida
6	2011	22	Idaho
7	2011	22	Idaho
8	2011	23	Idaho
9	2011	23	Idaho
10	2011	24	Florida
11	2011	24	Florida
12	2011	25	Florida
13	2011	25	Florida
Sheet1

Excel Workbook
	D	E	F	G	H
18	22	23	24	25
19	X	X	X	X	Florida
20	X	X	Missing	Missing	Idaho
Excel 2007 I am wanting to know whether or not a certain row is included in my data set. I have a matrix below where I want to have a "X" if that row of data for that week exists for the corresponding State and "Missing" if there is no row of data for that week for that State. Sheet1

#VALUE!

Excel 2007

Cell Formulas
Range	Formula
D19	=IF(INDEX($A$1:$A$13,MATCH(D18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
D20	=IF(INDEX($A$1:$A$13,MATCH(D18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")
E19	=IF(INDEX($A$1:$A$13,MATCH(E18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
E20	=IF(INDEX($A$1:$A$13,MATCH(E18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")
F19	=IF(INDEX($A$1:$A$13,MATCH(F18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
F20	=IF(INDEX($A$1:$A$13,MATCH(F18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")
G19	=IF(INDEX($A$1:$A$13,MATCH(G18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
G20	=IF(INDEX($A$1:$A$13,MATCH(G18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")

Here is the result I am getting:

Excel Workbook
	D	E	F	G	H
18	22	23	24	25
19	X	X	X	X	Florida
20	X	X	X	X	Idaho
Sheet1

Excel 2007

Cell Formulas
Range	Formula
D19	=IF(INDEX($A$1:$A$13,MATCH(D18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
D20	=IF(INDEX($A$1:$A$13,MATCH(D18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")
E19	=IF(INDEX($A$1:$A$13,MATCH(E18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
E20	=IF(INDEX($A$1:$A$13,MATCH(E18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")
F19	=IF(INDEX($A$1:$A$13,MATCH(F18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
F20	=IF(INDEX($A$1:$A$13,MATCH(F18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")
G19	=IF(INDEX($A$1:$A$13,MATCH(G18,$B$1:$B$13,0),AND(MATCH($H$19,$C$1:$C$13,0)))=2011,"X","")
G20	=IF(INDEX($A$1:$A$13,MATCH(G18,$B$1:$B$13,0),AND(MATCH($H$20,$C$1:$C$13,0)))=2011,"X","")

There should not be a "X" for Idaho for Weeks 24 and 25. Is this even possible to do with just formulas or would I need to go the VBA route?

Thanks for the help!

 

[Matty]

Does Year need to be considered as well? Or just Week and Place?

Matty

 

[Matty]

If just Week and Place, try:

=IF(ISNUMBER(MATCH(1,IF($B$2:$B$13=D$18,IF($C$2:$C$13=$H19,1)),0)),"X","Missing")

Array entered in D19, i.e. with CTRL+SHIFT+ENTER, and then copied down and across.

Matty

 

[turner38]

Just the week and place.

 

[Matty]

Just the week and place.

Cool. See my last post...

Matty