x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 195

Need help with a complicated lookup

I have a xls workbook with a lot of tabs

the two key tabs are my "all deals" and "mapping" tabs

In "all deals"
I am trying to get column K to be equal to column C of mapping when:
all deals C = mapping A
AND
all deals J = mapping B

I need this to be a real time lookup so if anything changes it happens in all deals

Also all deals J is a lookup field as well
=IF(OR(LEFT(B2,3)="CS-",LEFT(B2,4)="CS -"),"Federal CS",LOOKUP(9.9999E+307,SEARCH({"Civilian","CONSULT","Defense","Navy","Healthcare","Sled"},I2),{"Civilian","USPS Intel","Defense/Fedsec","Navy/Marine Corp","Healthcare","S&L"}))
0
Matt Pinkston
• 5
• 4
1 Solution

Commented:
is column C of Mapping numeric or text?
0

Commented:
IF you put this in K3 of 'all deals,' does it work?

=IF(AND(C3=mapping!A3,J3=mapping!B3),mapping!C3,"")
0

Author Commented:
column C of mapping is a text field
0

Author Commented:
AER78 no does not work

In "all deals"
I am trying to get column K to be equal to column C of mapping when:
all deals C = mapping A
AND
all deals J = mapping B
0

Commented:
Try something like this:

=INDEX(Mapping!\$C\$2:\$C\$100,MATCH(1,INDEX((Mapping!\$A\$2:\$A\$100=A2)*(Mapping!\$B\$2:\$B\$100=B2),0),0))

adjust ranges to suit...
0

Author Commented:
NBVC sorry the formula only returns #/NA
0

Commented:
Is there an exact match?  Check for extra spaces in the cells that you think should return a match.
0

Commented:
Also, I assume you adjusted the references?

Perhaps:

=INDEX(Mapping!\$C\$2:\$C\$100,MATCH(1,INDEX((Mapping!\$A\$2:\$A\$100=C2)*(Mapping!\$B\$2:\$B\$100=J2),0),0))

according to your original query.
0

Author Commented:
sample file
sample-for-EE.xlsx
0

Commented:
I am not sure what you are showing.  It seems that you do have results in several cells.  The #N/A cells mean there is no match... and you've added the IFERROR() to capture those are return a blank instead.

Can you point to one that you know should show a match?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Featured Post

• 5
• 4
Tackle projects and never again get stuck behind a technical roadblock.