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:
• 7
• 4
• 3
1 Solution

Commented:
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 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

Author Commented:
It complains about the \$q1
0

Commented:
Is the value in Column R numeric?
0

Author Commented:
No they are all text
0

Commented:
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

Commented:
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 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

Commented:
Could you post a sample of the data so I see more closely how to address the issue?
0

Commented:
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 Commented:
That works perfectly you are a savior!!!
0

Author 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

Commented:
I think cyimxtck closed the question incorrectly.
0

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

## Featured Post

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