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(T
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!
Here is what I have which doensn't work:
=IF(AND(NOT(ISNA(VLOOKUP(T
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!
ASKER
Says the formula is invalid. If I change it to this:
=INDEX($R:$R,MATCH(TRIM(A2
I get #VALUE??
=INDEX($R:$R,MATCH(TRIM(A2
I get #VALUE??
ASKER
It complains about the $q1
Is the value in Column R numeric?
ASKER
No they are all text
My bad, it should be the following, still entered with Ctrl+shift+Enter
=INDEX($R:$R,MATCH(trim(a2
=INDEX($R:$R,MATCH(trim(a2
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
lvbarnes
Book2.xlsx
ASKER
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
How can I implement the TRIM function to do this? Is there a way?
Thanks
Could you post a sample of the data so I see more closely how to address the issue?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That works perfectly you are a savior!!!
ASKER
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! :)
Accepted answer: 0 points for cyimxtck's comment #a39669907
for the following reason:
Very efficient and I could use this person around the office! :)
I think cyimxtck closed the question incorrectly.
ASKER
Sorry about that, I clicked me instead of you!
=INDEX($R:$R,MATCH(trim(a2
Thomas