Solved

Excel lookup based on two columns...

Posted on 2013-11-22
14
324 Views
Last Modified: 2013-11-22
I am trying to compare columns A1 and B1 to Columns P and Q respectively and if they match I want column R.

Here is what I have which doensn't work:

=IF(AND(NOT(ISNA(VLOOKUP(TRIM(A2),$P:$P,1,FALSE)))=TRUE,NOT(ISNA(VLOOKUP(TRIM(B2),$Q:$Q,1,FALSE))))=TRUE,$R:R(ROW),"X")

Problem is that I don't know what the row # is in R since it could match on 121 or 2 or...

So trying to use the row function didn't work out.  I need help with the value resulting from true ($R:R(ROW))

If the value evaluates to true I want the corresponding row value for column R.

Thanks in advance!
0
Comment
Question by:cyimxtck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 3
14 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39669480
Use a combined index/match in an array formula, as follows, entered with Ctrl+Shift+Enter

=INDEX($R:$R,MATCH(trim(a2)&trim(b2),$P:$p&$q:$q1,0))

Thomas
0
 

Author Comment

by:cyimxtck
ID: 39669699
Says the formula is invalid.  If I change it to this:

=INDEX($R:$R,MATCH(TRIM(A2)&TRIM(B2),$P:$P&$Q:$Q,1))

I get #VALUE??
0
 

Author Comment

by:cyimxtck
ID: 39669701
It complains about the $q1
0
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!

 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39669803
Is the value in Column R numeric?
0
 

Author Comment

by:cyimxtck
ID: 39669832
No they are all text
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39669833
My bad, it should be the following, still entered with Ctrl+shift+Enter

=INDEX($R:$R,MATCH(trim(a2)&trim(b2),$P:$p&$q:$q,0))
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39669838
Attached is a spreadsheet with an example of a sumifs (if the value is a number) or an array if it is alpha.

lvbarnes
Book2.xlsx
0
 

Author Comment

by:cyimxtck
ID: 39669859
Ok, I got that to work thankfully but there are some values that have spaces in them and are not evaluating correctly.  If I TRIM() the array they all value falsely to TRUE....

How can I implement the TRIM function to do this?  Is there a way?

Thanks
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39669878
Could you post a sample of the data so I see more closely how to address the issue?
0
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 500 total points
ID: 39669885
Trim function added to both source and lookup.  
=INDEX($S$2:$S$7,MATCH(TRIM(A2)&TRIM(B2),TRIM($P$2:$P$7)&TRIM($Q$2:$Q$7),0))
Remember Ctrl + Shift + Enter.  Attachment included.
Book2.xlsx
0
 

Author Comment

by:cyimxtck
ID: 39669907
That works perfectly you are a savior!!!
0
 

Author Comment

by:cyimxtck
ID: 39669937
I've requested that this question be closed as follows:

Accepted answer: 0 points for cyimxtck's comment #a39669907

for the following reason:

Very efficient and I could use this person around the office!  :)
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39669938
I think cyimxtck closed the question incorrectly.
0
 

Author Closing Comment

by:cyimxtck
ID: 39670028
Sorry about that, I clicked me instead of you!
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question