Matthew has attended:
Excel Advanced course
Excel Intermediate course
Project Management - Framework & Processes course
XLOOKUP multiple values
Hi I would like advice on using XLOOKUP to return a value from table based on a test of 2 values. I have sheets that look like this.
A sheet of scoring with this structure.
Reference Applicant, Reviewer1 Score Reviewer2 Score
Z001 James Dee Mr Blue ??? Mr Pink ???
A sheet of reviews with this structure.
Reference Applicant Reviewer Score
Z001 James Dee Mr Blue 4.0
Z001 James Dee Mr Pink 5.2
Z002 Jo Red Mr Green 9.1
Z002 Jo Red Mr Yellow 5.7
The end result is this...
Reference Applicant, Reviewer1 Score Reviewer2 Score
Z001 James Dee Mr Blue 4.0 Mr Pink 5.2
In the scoring sheet I want to present the scores.
I have been trying to use XLOOKUP.
I believe I want to use XLOOKUP and then return the score where the reference is 'Z001' and the Reviewer is 'Mr Blue'.
Thus far I have only used XLOOKUP using only a single value for comparison.
And it this is Jens answering have a great weekend!
Cheers
RE: XLOOKUP multiple values
Hello Matthew,
Thank you for posting this interesting question.
To achieve this using XLOOKUP, you can combine it with the CONCATENATE function (or the & operator) to create a unique lookup value based on both the reference and the reviewer. Here's how you can do it:
1.Create a helper column in your reviews sheet that combines the Reference and Reviewer columns. For example, in column D, you can use the following formula:
=A2 & " " & C2
This will create a unique identifier like "Z001 Mr Blue".
2.Use XLOOKUP in your scoring sheet to find the score based on this unique identifier. Assuming your scoring sheet has the same structure, and you want to fill in the scores for Reviewer1 and Reviewer2, you can use the following formula in the cell where you want the score for Reviewer1:
=XLOOKUP(A2 & " " & C2, Reviews!D:D, Reviews!E:E, "Not Found")
And for Reviewer2:
=XLOOKUP(A2 & " " & E2, Reviews!D:D, Reviews!E:E, "Not Found")
Here's a step-by-step example:
1.In the Reviews sheet:
Add a helper column D with the formula =A2 & " " & C2.
This will create unique identifiers like "Z001 Mr Blue", "Z001 Mr Pink", etc.
2.In the Scoring sheet:
For Reviewer1 Score, use the formula =XLOOKUP(A2 & " " & C2, Reviews!D:D, Reviews!E:E, "Not Found").
For Reviewer2 Score, use the formula =XLOOKUP(A2 & " " & E2, Reviews!D:D, Reviews!E:E, "Not Found").
This approach ensures that you are looking up the score based on both the reference and the reviewer, giving you the correct score for each reviewer.
Let us know if you need any further assistance!
Kind regards
Marius Barnard
STL
