• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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
Asked:
Matt Pinkston
  • 5
  • 4
1 Solution
 
NBVCCommented:
is column C of Mapping numeric or text?
0
 
aer78Commented:
IF you put this in K3 of 'all deals,' does it work?

=IF(AND(C3=mapping!A3,J3=mapping!B3),mapping!C3,"")
0
 
Matt PinkstonAuthor Commented:
column C of mapping is a text field
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Matt PinkstonAuthor 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
 
NBVCCommented:
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
 
Matt PinkstonAuthor Commented:
NBVC sorry the formula only returns #/NA
0
 
NBVCCommented:
Is there an exact match?  Check for extra spaces in the cells that you think should return a match.
0
 
NBVCCommented:
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
 
Matt PinkstonAuthor Commented:
sample file
sample-for-EE.xlsx
0
 
NBVCCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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