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.


[TABLE="width: 281"]
<tbody>[TR]
[TD="width: 94, bgcolor: transparent"][/TD]
[TD="width: 94, bgcolor: transparent"][/TD]
[TD="width: 184, bgcolor: transparent, colspan: 6"]UPLOAD REPORT SHEET
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Column
[/TD]
[TD="bgcolor: transparent"]E
[/TD]
[TD="bgcolor: transparent"]J
[/TD]
[TD="bgcolor: transparent"]O
[/TD]
[/TR]
[TR]
[TD="width: 94, bgcolor: #209EA0"][/TD]
[TD="width: 94, bgcolor: #209EA0"]Project Number
[/TD]
[TD="width: 94, bgcolor: #209EA0"]Job Scope
[/TD]
[TD="width: 90, bgcolor: #209EA0"]Contractor
[/TD]
[/TR]
[TR]
[TD="width: 94, bgcolor: lightgrey"][/TD]
[TD="width: 94, bgcolor: lightgrey"]A01C7Y9
[/TD]
[TD="width: 94, bgcolor: lightgrey"]F1
[/TD]
[TD="width: 90, bgcolor: white"]#N/A
[/TD]
[/TR]
[TR]
[TD="width: 94, bgcolor: lightgrey"][/TD]
[TD="width: 94, bgcolor: lightgrey"]A01C7T3
[/TD]
[TD="width: 94, bgcolor: lightgrey"]F1
[/TD]
[TD="width: 90, bgcolor: white"]#N/A
[/TD]
[/TR]
[TR]
[TD="width: 94, bgcolor: lightgrey"][/TD]
[TD="width: 94, bgcolor: lightgrey"]A01C7M8
[/TD]
[TD="width: 94, bgcolor: lightgrey"]F1
[/TD]
[TD="width: 90, bgcolor: white"]#N/A
[/TD]
[/TR]
[TR]
[TD="width: 94, bgcolor: lightgrey"][/TD]
[TD="width: 94, bgcolor: lightgrey"]A01C6NV
[/TD]
[TD="width: 94, bgcolor: lightgrey"]F2
[/TD]
[TD="width: 90, bgcolor: white"]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 168"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 160, bgcolor: transparent, colspan: 2"]JUW SHEET
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A
[/TD]
[TD="bgcolor: transparent"]B
[/TD]
[TD="bgcolor: transparent"]C
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]F1
[/TD]
[TD="bgcolor: transparent"]F2
[/TD]
[TD="bgcolor: transparent"]Contractor
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A01C7Y9
[/TD]
[TD="bgcolor: transparent"]A01C6NV
[/TD]
[TD="bgcolor: transparent"]LGDESIGNS
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A01C7T3
[/TD]
[TD="bgcolor: transparent"]A01C0A5
[/TD]
[TD="bgcolor: transparent"]SMITHCORP
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A01C7M8
[/TD]
[TD="bgcolor: transparent"]A01C0AA
[/TD]
[TD="bgcolor: transparent"]BRENNER
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A01AAV9
[/TD]
[TD="bgcolor: transparent"]A01C0AH
[/TD]
[TD="bgcolor: transparent"]BRENNER
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]F1[/TD]
[TD="bgcolor: #FAFAFA"]F2[/TD]
[TD="bgcolor: #FAFAFA"]Contractor[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]A01C7Y9[/TD]
[TD="bgcolor: #FAFAFA"]A01C6NV[/TD]
[TD="bgcolor: #FAFAFA"]LGDESIGNS[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]A01C7T3[/TD]
[TD="bgcolor: #FAFAFA"]A01C0A5[/TD]
[TD="bgcolor: #FAFAFA"]SMITHCORP[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]A01C7M8[/TD]
[TD="bgcolor: #FAFAFA"]A01C0AA[/TD]
[TD="bgcolor: #FAFAFA"]BRENNER[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]A01AAV9[/TD]
[TD="bgcolor: #FAFAFA"]A01C0AH[/TD]
[TD="bgcolor: #FAFAFA"]BRENNER[/TD]

</tbody>

JUW​

Then the formula as written generates:

	E	J	O
LGDESIGNS
SMITHCORP
BRENNER
LGDESIGNS

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #209EA0"]Project Number[/TD]
[TD="bgcolor: #209EA0"]Job Scope[/TD]
[TD="bgcolor: #209EA0"]Contractor[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #D3D3D3"]A01C7Y9[/TD]
[TD="bgcolor: #D3D3D3"]F1[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #D3D3D3"]A01C7T3[/TD]
[TD="bgcolor: #D3D3D3"]F1[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #D3D3D3"]A01C7M8[/TD]
[TD="bgcolor: #D3D3D3"]F1[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #D3D3D3"]A01C6NV[/TD]
[TD="bgcolor: #D3D3D3"]F2[/TD]

</tbody>

Upload Report​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O2[/TH]
[TD="align: left"]=INDEX(JUW!$C$1:$C$51,MATCH(E2,INDEX(JUW!$A$1:$B$51,0,MATCH(J2,JUW!$A$1:$B$1,0)),0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!