How to do an IF VLOOKUP | IF and VLOOKUP Nested Function | IF TRUE Return Value From Another Column

In this video I demonstrate how to use the IF and VLOOKUP functions together in a single formula. The combination of functions allows you to return values from different columns or tables based on a condition. In our example, we want to apply the correct discount to a table of transactions.
The discount applied depends on two factors: the product category and quantity purchased. The quantity purchased defines which column in the lookup table we want to return the discount percentage from.

Although IF and VLOOKUP work well for three-column lookup tables, the combination of INDEX and MATCH is better for larger ones. For this reason, I also give an example formula using INDEX with nested MATCH functions.

Excel 365 users will benefit from the IF XLOOKUP and INDEX XMATCH examples I also demonstrate in this video.
------------------------

[imranisshack]

This is so helpful. One of the few videos on this topic

[bharatrangwani]

Thanks for making me 50% more efficient.

[ashleytate-yoder]

I could not for the life of me figure this out for my excel class. I watched so many videos and nothing was helping. Thank you so much.

[excel_along_the_way]

Hi Chester, thank you for the video. I would have used IF to return the Col index no rather then to use VLOOKUP twice. I do enjoy your videos please keep it up.

[patrickschardt]

Wonderful tutorial. Only thing I would possibly change is put the if statement in the column index saving the additional vlookup (or the return array for xlookup) since the lookup value and the lookup range are the same in both cases

[IvanCortinas_ES]

Excellent video Chester. A very good comparison. Thank you.

[CraigDG]

Thanks for this great info Chester. Wondering if an IF statement can be nested within an XLOOKUP function? The XLOOKUP function I am using works well, but i need to change the result that sources the Return Array number, into a Text String instead. I could add an extra column and use the IF function in that new column, however, I would like to have my XLOOKUP and the IF in the same formula. Any tips would be most appreciated. Thanks.

[AnthonyHann]

I'm struggling with something i'm working on. I have a list of locations on one sheet and a number of tables on the second sheet. I want a lookup that, if it can find the corresponding location it returns the table for that location

[na]

How can I use your formulas for multiple if statements? Basically, two or more if statements that can all be true or one is true... if A is true then "abc", if B is true then "def", If C is true then "ghi"....all three needs to be tested and if all true...then print abc next line def and next line ghi. If none is true then blank...

[k.k.sabariraj]

hi sir, thank you for this video presentation. here I need one more vba code for this formula

=if(a2=VLOOKUP(a2, table1, 1, true), VLOOKUP(a2, table1, 2, true), "NA")

applying this formula for the above 4 lacks data we vlookup the values from the next sheet table data

waiting for your code

thank you.