INDEX MATCH to search two different columns and return value

[kkbakic]

I'm trying to write a formula that will return a value from another sheet. My Main Table has a list of project numbers that I need to fill in the assigned contractor name from another sheet. The problem is that there are two project numbers (categorized as F1 & F2) on each row in different columns. I would like my formula to check both columns and return the contractor name.

In other words -- my main table has both F1 & F2 project numbers on separate rows in the same column. My lookup table has the F1 & F2 numbers on the same row in two different columns.

Main Table w formula in column O - F1 & F2 numbers are in column E. Lookup Table has F1 numbers in column A; and F2 numbers in Column B. I want to fill in Column O with the Contractor Name that is in column C on the LookUp table. Here's what I have, but cant get it to work:

=INDEX(JUW!$C$1:$C$51,MATCH(E2,JUW!$A$1:$A$51,0), MATCH(E2,JUW!$B$1:$B$51,0))
​

*E2 is on a different sheet called "Upload Report" which is the sheet that has the formula in Column O.

		UPLOAD REPORT SHEET
Column	E	J	O
	Project Number	Job Scope	Contractor
	A01C7Y9	F1	#N/A
	A01C7T3	F1	#N/A
	A01C7M8	F1	#N/A
	A01C6NV	F2	#N/A

<tbody>
</tbody>

	JUW SHEET
A	B	C
F1	F2	Contractor
A01C7Y9	A01C6NV	LGDESIGNS
A01C7T3	A01C0A5	SMITHCORP
A01C7M8	A01C0AA	BRENNER
A01AAV9	A01C0AH	BRENNER

<tbody>
</tbody>

 

[Eric W]

Try:

=INDEX(JUW!$C$1:$C$51,MATCH(E2,INDEX(JUW!$A$1:$B$51,0,MATCH(J2,JUW!$A$1:$B$1,0)),0))

 

[kkbakic]

Sorry Eric, it still didn't work - even after I changed J2 to E2
=INDEX(JUW!$C$1:$C$51,MATCH(E2,INDEX(JUW!$A$1:$B$51,0,MATCH(E2,JUW!$A$1:$B$51,0)),0))

I also tried
=INDEX(JUW!$C$1:$C$51,MATCH(E2,INDEX(JUW!$A$1:$A$51,0,MATCH(E2,JUW!$B$1:$B$1,0)),0))
at least with this I get n #REF ! instead of #N/A

 

[Eric W]

Given this JUW sheet:

	A	B	C
1	F1	F2	Contractor
2	A01C7Y9	A01C6NV	LGDESIGNS
3	A01C7T3	A01C0A5	SMITHCORP
4	A01C7M8	A01C0AA	BRENNER
5	A01AAV9	A01C0AH	BRENNER

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

JUW​

Then the formula as written generates:

	E	J	O
1	Project Number	Job Scope	Contractor
2	A01C7Y9	F1	LGDESIGNS
3	A01C7T3	F1	SMITHCORP
4	A01C7M8	F1	BRENNER
5	A01C6NV	F2	LGDESIGNS

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Upload Report​

Worksheet Formulas Cell Formula O2 =INDEX(JUW!$C$1:$C$51,MATCH(E2,INDEX(JUW!$A$1:$B$51,0,MATCH(J2,JUW!$A$1:$B$1,0)),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The J2 in the formula is intentional to decide which column (A or B) to get the Contractor from.

 

[kkbakic]

Oh Geez, I didn't even think of using that column as a qualifier. The headings on my actual worksheet were slightly different so it wasn't working, but that was easy enough to change. Works like a charm now! Thanks so much.

 

[Eric W]

Glad to help!