VLOOKUP with IF statement in Google Sheets

In Google Sheets, combine VLOOKUP with IF to get a result conditional on the value returned by VLOOKUP (yes/no, true/false, ...).
In this example, we'll check if a product is in stock based on its remaining amount:

Formula in G4
=IF(VLOOKUP(F4,B$4:D$7,2,FALSE)>0,"Yes","No")
A
B
C
D
E
F
G
H
1
2
1st column
2nd column
3rd column
3
Product
Amount
Price
Product
In stock
4
Apple
24
$0.52
Yes
5
Banana
30
$0.24
👆 Try changing this value
6
Kiwi
0
$0.63
7
Lemon
41
$0.17
8
Open in Google Sheets
Show arrows
Separator

Procedure

  1. Build the VLOOKUP formula:
    1. Enter =VLOOKUP in cell G4, where you want to see if a Product is in Stock or not.
    2. Enter the Lookup value F4, which contains the Product (Banana) you want to look for.
    3. Enter the Search range B$4:D$7, the range of data that contains all the Product and Amount values.
      You can add the dollar sign ($) to fix the rows, so if you drag down the formula to check the stock of other products, the IF formula will always refer to the same rows.
    4. Enter Column number 2, as the Amount column is the 2nd column of the Search range.
    5. Enter FALSE to look for an exact match.
  2. Build the IF formula around the VLOOKUP formula:
    1. Enter IF( between the = and VLOOKUP formula.
      The formula looks like this: =IF(VLOOKUP(F4, B$4:D$7, 2, FALSE)
    2. Enter >0 right after the VLOOKUP formula.
      The formula looks like this: =IF(VLOOKUP(F4, B$4:D$7, 2, FALSE)>0
      The Test value of the IF formula says that the result of the VLOOKUP formula is greater than 0.
    3. Enter "Yes", which defines if the Test value returns TRUE.
    4. Enter "No", which defines if the Test value returns FALSE.

Result

The final formula is: =IF(VLOOKUP(F4, B$4:D$7, 2, FALSE)>0, "Yes", "No")
The IF formula finds that the Amount of Banana is greater than 0, so it returns TRUE. The value that appears in G4 is Yes.

Show reference
← PreviousHow to use VLOOKUP from another sheetNext →VLOOKUP partial match