Lookup formulas pull information from a table - an operator's grade from a skill list, an operation's SMV from a bulletin, a style's buyer from an order sheet. These five cover almost every case.
Example table (named data):
| Serial | Name | Age | Address | Salary |
|---|---|---|---|---|
| 1 | Nasir | 24 | Dhaka | 1500 |
| 2 | Zaheer | 25 | Dhaka | 2313 |
| 3 | Ahmed | 32 | Sylhet | 2354 |
| 4 | Shahed | 27 | Ctg | 354 |
VLOOKUP - search down the first column
=VLOOKUP(lookup_value, table, column_number, FALSE)
=VLOOKUP(3, data, 2, FALSE) → Ahmed (finds 3 in the first column, returns column 2).
- FALSE = exact match (use this almost always).
- TRUE = closest smaller value - only for sorted ranges like grade bands.
HLOOKUP - search across the top row
The same idea for tables laid out sideways: it looks in the top row and returns a value from a row you choose.
=HLOOKUP("Salary", data2, B13+1, FALSE) - the example adds 1 to the serial number because the first row of the range is the header.
INDEX - pick by row and column number
=INDEX(data, 3, 5) → 2354 (row 3, column 5).
MATCH - find the position
=MATCH("Shahed", B2:B5, 0) → 4 (Shahed is the 4th item). The 0 means exact match.
INDEX + MATCH - the flexible lookup
=INDEX(E2:E5, MATCH("Zaheer", B2:B5, 0)) → 2313
Why engineers prefer it:
- the lookup column does not need to be on the left
- inserting columns does not break it
- it is faster on big sheets
Tip: In Excel 365,
XLOOKUPdoes the same in one function:=XLOOKUP("Zaheer", B2:B5, E2:E5).
CHOOSE - switch between options by number
=SUM(CHOOSE(C10, G7:G14, H7:H14)) sums G7:G14 when C10 is 1 and H7:H14 when C10 is 2 - handy for a report where the user picks "1 = this month, 2 = last month" from a drop-down.
Garment examples
| Need | Formula idea |
|---|---|
| SMV of an operation from the bulletin | VLOOKUP(operation, bulletin, smv_col, FALSE) |
| Operator grade from ID | INDEX(grades, MATCH(id, ids, 0)) |
| Which column holds today's date in an hourly report | MATCH(TODAY(), dates_row, 0) |