Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

xlookup

ResolvedVersion 365

Altab has attended:
Excel Advanced course

Xlookup

Hi,

I have two sets of data that I need to look up, I wanted to use Xlookup and then and/or function to look at another field to return a result.

Thanks

RE: Xlookup

Hello Altab,

Thank you for your post.

I hope I understand your question correctly. If so, we can break it down step by step.

Step 1: Using XLOOKUP
First, you'll use the `XLOOKUP` function to find the value you need from your first set of data. The syntax for `XLOOKUP` is:

=XLOOKUP(lookup_value, lookup_array, return_array)

For example, if you want to look up a value in column A and return the corresponding value from column B, you would use:

=XLOOKUP(A2, A:A, B:B)

Step 2: Combining with AND/OR
Next, you can use the `AND` or `OR` functions to evaluate another condition. Here’s how you can combine them:

Using AND
If you want to check if both conditions are true, you can nest the `AND` function within an `IF` statement:

=IF(AND(XLOOKUP(A2, A:A, B:B) = "Value1", C2 = "Value2"), "Result1", "Result2")

This formula checks if the value found by `XLOOKUP` is "Value1" and if the value in column C is "Value2". If both conditions are true, it returns "Result1"; otherwise, it returns "Result2".

Using OR
If you want to check if either condition is true, you can nest the `OR` function within an `IF` statement:

=IF(OR(XLOOKUP(A2, A:A, B:B) = "Value1", C2 = "Value2"), "Result1", "Result2")

This formula checks if either the value found by `XLOOKUP` is "Value1" or the value in column C is "Value2". If either condition is true, it returns "Result1"; otherwise, it returns "Result2".

Example
Let's say you have two sets of data:
- **Set 1**: Column A (IDs), Column B (Names)
- **Set 2**: Column C (IDs), Column D (Status)

You want to look up the name from Set 1 based on the ID in Set 2 and check if the status is "Active". You can use:

=IF(AND(XLOOKUP(C2, A:A, B:B) = "John Doe", D2 = "Active"), "Match Found", "No Match")

This formula will return "Match Found" if the name corresponding to the ID in column C is "John Doe" and the status in column D is "Active".

Feel free to adjust the ranges and values to fit your specific data. If you have any more questions or need further assistance, just let us know!

Kind regards
Marius Barnard
STL

 

Training courses

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Hiding Formulae in the Formula Bar

It is possible to protect the contents of a cell reference(s) from amendment by applying cell protection. The contents of the cell reference can also be stopped from displaying in the formula bar.

Step 1: Select Format > Cells > Protection.

Step 2: Tick Hidden option. Ensure Locked is ticked

Step 3: Select Tools > Protection > Protect Sheet

(Ensure "Protect worksheet and contents of locked cells" is ticked)


View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.