Lookup question

[steve400243]

Hello, When I enter The City in Cell M2, and State in N2, I need to return the value from Column I in cell O2 as shown above. Thanks for all the help.

 

[StephenCrump]

	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Aberdeen	NJ								Aberdeen	WA	US GHR
3	Aberdeen	NC										US GHR
4	Aberdeen	SC
5	Aberdeen	WA	US GHR
6	Aberdeen	ID
7	Aberdeen	OH
8	Aberdeen	MS
9	Aberdeen	KY
10	Springfield	AR
11	Springfield	CA
12	Springfield	CO
Sheet1

Cell Formulas
Range	Formula
O2	O2	=INDEX(F2:F12,MATCH(1,(D2:D12=M2)*(E2:E12=N2),))
O3	O3	=FILTER(F2:F12,(D2:D12=M2)*(E2:E12=N2))

 

[PeteWright]

Try this:

MrExcel_2025-01.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
1										City	State	Value
2	Aberdeen	NJ				US AB3				Aberdeen	WA	US GHR
3	Aberdeen	NC				US UAE
4	Aberdeen	SC				US XBA
5	Aberdeen	WA				US GHR
6	Aberdeen	ID				US APD
7	Aberdeen	OH				US AHD
8	Aberdeen	MS				US AEK
9	Aberdeen	KY				US ABK
Sheet1

Cell Formulas
Range	Formula
O2	O2	=INDEX(I2:I9,XMATCH(M2&N2,D2:D9&E2:E9))

 

[steve400243]

	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Aberdeen	NJ								Aberdeen	WA	US GHR
3	Aberdeen	NC										US GHR
4	Aberdeen	SC
5	Aberdeen	WA	US GHR
6	Aberdeen	ID
7	Aberdeen	OH
8	Aberdeen	MS
9	Aberdeen	KY
10	Springfield	AR
11	Springfield	CA
12	Springfield	CO
Sheet1

Cell Formulas
Range	Formula
O2	O2	=INDEX(F2:F12,MATCH(1,(D2:D12=M2)*(E2:E12=N2),))
O3	O3	=FILTER(F2:F12,(D2:D12=M2)*(E2:E12=N2))

[/RANGE

Cell Formulas
Range	Formula

Thanks for looking at this Stephen, Here is my results using your formula and changing to Column I for the results

 

[steve400243]

=INDEX(I2:I12,MATCH(1,(D2:D12=M2)*(E2:E12=N2),)) in cell O2
=FILTER(I2:I12,(D2:D12=M2)*(E2:E12=N2)) In Cell O3

 

[steve400243]

Try this:

MrExcel_2025-01.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
1										City	State	Value
2	Aberdeen	NJ				US AB3				Aberdeen	WA	US GHR
3	Aberdeen	NC				US UAE
4	Aberdeen	SC				US XBA
5	Aberdeen	WA				US GHR
6	Aberdeen	ID				US APD
7	Aberdeen	OH				US AHD
8	Aberdeen	MS				US AEK
9	Aberdeen	KY				US ABK
Sheet1

Cell Formulas
Range	Formula
O2	O2	=INDEX(I2:I9,XMATCH(M2&N2,D2:D9&E2:E9))

Thanks for looking at this Pete, I get an "N/A code using your formula.

 

[StephenCrump]

All three formulae rely on finding an exact match.

Do you perhaps have leading or trailing space characters anywhere, e.g. "Aberdeen " or "WA " rather than "Aberdeen" and "WA"?

 

[steve400243]

That Did it, Thanks so much for your Help.

 

[steve400243]

Try this:

MrExcel_2025-01.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O
1										City	State	Value
2	Aberdeen	NJ				US AB3				Aberdeen	WA	US GHR
3	Aberdeen	NC				US UAE
4	Aberdeen	SC				US XBA
5	Aberdeen	WA				US GHR
6	Aberdeen	ID				US APD
7	Aberdeen	OH				US AHD
8	Aberdeen	MS				US AEK
9	Aberdeen	KY				US ABK
Sheet1

Cell Formulas
Range	Formula
O2	O2	=INDEX(I2:I9,XMATCH(M2&N2,D2:D9&E2:E9))

This worked as needed also Pete, After removing extra space characters. Thank you for the help.