Excel LOOKUP Function Only PROS Use (simple to complex examples)

The LOOKUP function in Microsoft Excel has the ability to return data from any column that you specify, including one to the left of the column you are looking within. Its default behavior makes it useful for solving certain problems in Excel. The problem with this function is that it can return wrong results if it's not used properly. That's why many people use the VLOOKUP function instead of LOOKUP. However once you understand why you're getting wrong results and how the Lookup function really works, you can use it to solve simple to complex lookup tasks in Excel.

🌟 Key Learning Points:
- LOOKUP Basics: Discover the basic usage of the LOOKUP function, including its arguments and how to apply it for simple data like employee bonuses based on salary tiers.
- Sorting Requirement: Understand the critical importance of having your lookup vector sorted in ascending order for accurate results.
- Advanced LOOKUP: Learn advanced techniques to find hierarchical data, such as immediate parent part numbers in a Bill of Materials (BOM).
- Custom Lookup Vectors: Explore how to create custom lookup vectors using a combination of TRUE/FALSE values, errors, and numerical conversions to solve complex problems.
- Error Handling: Gain insights into using IFERROR for cases where the LOOKUP function might return errors.
- XLOOKUP Alternative: Learn about the newer XLOOKUP function, offering more flexibility and ease, especially for searching last to first within a range.

00:00 How to Use Excel LOOKUP Function
00:39 LOOKUP Basics
04:42 LOOKUP Advanced
10:00 Alternative XLOOKUP
11:21 Wrap Up

🎬 LINKS to related videos:

🚩Let’s connect on social:

This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#Excel

[dragonshadow]

I learned something you probably wasnt expecting to teach. I never realized you could select part of the function in the formula bar and press f9 to get the true/false of the equation. I always used the step through process to determine the results.

[thecriticalpoint]

Such clever approaches to solving every stumbling block I've ever come across in Excel can be found on your channel. Plus all the problems I didn't even know I had.

You're the best Excel Unicorn on YouTube.

[siyabulelatshali]

😭😭😭You make me fall in love with Excel everytime you post a new video. Thank you.

[JoseAntonioMorato]

Dear Leila,
The LOOKUP function allows the 'lookup_value' to also be a vector and not a single cell.
To find the Bonus: =LOOKUP(B2:B5; G2:G6; H2:H6)
To find ID =LOOKUP(A2:A5; M2:M11; K2:K11) 🤗

[mcd]

Nice to see old school LookUp getting some attention and love.

[Excelambda]

Great video!!
If the lookup vector is an array, values of last column of that array will be returned, so no need for 3rd argument.
Spreadsheet "Lookup" cell C2 :
=LOOKUP(B2:B5, G2:H6)
Spreadsheet "Lookup Advanced", single cell formula in C2:

[monishtabla]

Leila you are too good, had trouble with LOOKUP, but no other video explained that data needs to be sorted, also learned the F4🙏

[pravinr]

You explained the logical part beautifully. The simplified lookup function in office 365.

[catherinegrimes]

Thank you very much Leila. On finding the LOOKUP function it seemed to be exactly what I wanted and couldn't get it to work. On watching this video, I could not believe that the list needed to be ordered. The Microsoft description of the function didn't mention that, but it was mentioned in small print towards the end of the help page.
Watching this video saved me a lot of time on this useless function.

[Bob_H]

You did it again. You took an overlooked function and delivered a great result. Although th XLookup is better the thought behind the Lookup is far supperior.

[TheMega]

You’ve increased my confidence so much. I appreciate you and I hope all is well

[ultrarageman]

The SORTING part is the think that I was looking for. Thank you.

[akongnwimacbright]

Thank you very much for this. I have been trying to write this formula for two days now and I keep getting the wrong result until now, I've learned that my list has to be sorted out in ascending order. thanks again.

[Chess_Tales]

The real fun happens when you nest multiple xlookup functions together. The most I have done so far is 3 nested together, and it works like a charm.

The beginning of the formula looks like:



The result from your first xlookup (the furthest to the right) serves as the reference value for the second and so on. This allows you to lookup a value in one table, search for the match in a second table, and then take those matches and search for a match in a third table.

[CesaR-nqqq]

I learned x lookup from you and makes my life easier.

It's requires a lot of CPU processing power though (in large amount of cells).

[emile]

Hello ! thanks for the explanations. My only use of LOOKUP until now was to find the last non blank value in a range with some blanks. If the non blanks values were numbers, I used =LOOKUP(9^9;$A$2:A3), if they were text =LOOKUP("ZZZZ";$A$2:A3)

[chrism]

Thanks Leila. Very interesting, I almost never use this but it's good to know!

[ridgmont]

I would prefer to see the Bonus % being higher for the lower paid so that the resulting bonus amount is similar!!!

Good explanation thank you.

[wayneedmondson]

Awesome Leila! Thanks for the detailed demonstration and lookup tricks. Thumbs up!!

[johnhackwood]

Hi Leila great to see Lookup getting a bit limelight!
I have used it to avoid a nested IF say where you convert a lookup value to an index number CHOOSE can use to turn on the formula to apply e.g.
=CHOOSE(LOOKUP([@[Major Cost Type]], {"Beams", "Piers", "Slabs"}, {1, 2, 3}), fx1, fx2, fx3)
Its so simple to embed in a formula but you do have to have the list in alphabetical order I think from memory. Maybe IFS() is a better option now I haven't decided. But I like LOOKUP, it's simple and reads well in a formula.