Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Excel lookup based on two columns...

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
cyimxtck
Asked:
cyimxtck
  • 7
  • 4
  • 3
1 Solution
 
nutschCommented:
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
 
cyimxtckAuthor Commented:
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
 
cyimxtckAuthor Commented:
It complains about the $q1
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Lawrence BarnesCommented:
Is the value in Column R numeric?
0
 
cyimxtckAuthor Commented:
No they are all text
0
 
nutschCommented:
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
 
Lawrence BarnesCommented:
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
 
cyimxtckAuthor Commented:
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
 
nutschCommented:
Could you post a sample of the data so I see more closely how to address the issue?
0
 
Lawrence BarnesCommented:
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
 
cyimxtckAuthor Commented:
That works perfectly you are a savior!!!
0
 
cyimxtckAuthor Commented:
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
 
Lawrence BarnesCommented:
I think cyimxtck closed the question incorrectly.
0
 
cyimxtckAuthor Commented:
Sorry about that, I clicked me instead of you!
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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