asked on

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"}))

NBVC

is column C of Mapping numeric or text?

aer78

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

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

Matt Pinkston

ASKER

column C of mapping is a text field

Matt Pinkston

ASKER

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

NBVC

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...

Matt Pinkston

ASKER

NBVC sorry the formula only returns #/NA

NBVC

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

ASKER CERTIFIED SOLUTION

NBVC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Matt Pinkston

ASKER

sample file
sample-for-EE.xlsx

NBVC

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?