Logic and Lookup Functions

Introduction

So far formulas have done arithmetic. Real spreadsheets also decide and look things up. This lesson uses the sales table from the basics course, with Revenue in column F (rows 2 to 9: 20, 10, 37.5, 50, 16, 25, 30 and 60).

IF: choose between two results

=IF(F2>=30, "High", "Low")

Read it as: if the condition is true, give the first result, otherwise the second. Copied down rows 2 to 9, it returns Low, Low, High, High, Low, Low, High, High, so four rows are High.

Text in formulas goes in double quotes. Comparison signs are =, <> (not equal), <, >, <= and >=.

AND and OR

Combine conditions:

=IF(AND(B2="N", F2>=30), "Check", "")
=IF(OR(C2="Bag", C2="Book"), "Gift", "Stationery")

AND needs all conditions to be true. OR needs at least one. You can nest IFs for several outcomes, but long chains get hard to read. Consider a lookup table instead.

VLOOKUP: find a value in a table

Suppose a second table lists product prices in H2:I4:

To fetch the price for the product in C2:

=VLOOKUP(C2, $H$2:$I$4, 2, FALSE)

• The first input is what to find (C2).
• The second is the table, locked with dollar signs so it does not shift when copied.
• The third is the column number to return (2 means the second column of the table).
• FALSE means exact match, which is almost always what you want.

VLOOKUP searches the leftmost column of the table, and can only return values to the right.

INDEX and MATCH: more flexible

=INDEX($I$2:$I$4, MATCH(C2, $H$2:$H$4, 0))

MATCH finds the position of the product in the list (Pen is 1, Book is 2, Bag is 3), and INDEX returns the value at that position from another column. The last input 0 means exact match. This works in either direction, and does not break if you insert columns.

Newer versions of Excel, Google Sheets and LibreOffice also offer XLOOKUP, which is simpler and more flexible. Check that your program supports it, and that others who use your file have it too.

IFERROR: handle missing matches

If a lookup finds nothing, it shows #N/A. Replace it with something helpful:

=IFERROR(VLOOKUP(C2, $H$2:$I$4, 2, FALSE), "Not in price list")

Use it carefully. It hides all errors, including mistakes you should notice. Prefer to fix the cause when you can.

Good practice

• Keep lookup tables on their own sheet or area, with clear headings.
• Spell consistently, because "Pen" and "Pen " (with a space) will not match. TRIM can clean text.
• Use named ranges (such as PriceList) to make formulas readable.
• Test with a value you know is missing.

Try it yourself

Add the price table, then write a lookup that fills the Price column from the product name, and a formula for Revenue using it. Add an IF that labels each row "High" for revenue of 30 or more. Check that you get four High rows.

Summary

• IF chooses between results, and AND and OR combine conditions.
• VLOOKUP, or INDEX with MATCH, fetches matching values from another table, using exact match.
• IFERROR handles missing matches, but use it carefully so you do not hide real problems.