SumIf function is: SumIf( range, criteria, sum range )

Look up values vertically in a list by using an exact match

To do this task, use the VLOOKUP function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

1 2 3 4 5 6 7

A B C D Badge Number Last Name First Name Extension ID-34567 Davolio Nancy 5467 ID-16782 Fuller Andrew 3457 ID-4537 Leverling Janet 3355 ID-1873 Peacock Margaret 5176 ID-3456 Buchanan Steven 3453 ID-5678 Suyama Michael 428 Formula Description (Result) =VLOOKUP("ID-4537", A1:D7, 4, FALSE) Lookup the badge number, ID-4537, in the first column and return the matching value in the same row of the fourth column (3355)

Look up values vertically in a list by using an approximate match

To do this task, use the VLOOKUP function.

Important This method only works if the values in the first column have been sorted in ascending order.

Example

The example may be easier to understand if you copy it to a blank worksheet.

In this example, you know the frequency and want to look up the associated color.

1 2 3 4 5 6

A B Frequency Color 4.14 red 4.19 orange 5.17 yellow 5.77 green 6.39 blue Formula Description (Result) =VLOOKUP(5.93, A1:B6, 2, TRUE) Looks up 5.93 in column A, finds the next largest value that is less than 5.93, which is 5.77, and then returns the value from column B that's in the same row as 5.77 (green)

Look up values horizontally in a list by using an exact match

To do this task, use the HLOOKUP function.

Example

The example may be easier to understand if you copy it to a blank worksheet.

1 2 3 4

A B C Status Axles Bolts In stock 4 9 On order 5 10 Back order 6 11 Formula Description (Result) =HLOOKUP("Bolts", A1:C4, 3)

Combine IF and VLOOKUP

You can use an IF formula with an Excel VLookup formula, to return exact values if found, and an empty string if not found.

On the Invoice sheet, in cell A8, enter the product code A28. If the VLookup formula in cell B8 has FALSE as the fourth argument, the result is #N/A, because there is no exact match for the product code in the lookup table.

Wrap the VLookup formula with an IF formula (in this example the product list has been named), using the ISNA function to check for an #N/A error:

  =IF(ISNA(VLOOKUP(A8,ProductList,2,FALSE)),"",VLOOKUP(A8,ProductList,2,FALSE))

Press the Enter key, and cell appears blank. Because no exact match was found, the VLookup formula returned an #N/A, so the ISNA function result is TRUE. The IF formula converted this to an empty string.

If the lookup table contains any blank cells, a VLOOKUP formula will return a zero, instead of a blank cell. You can use nested IFs to handle the #N/A results, and the empty cell results. For example:
      =IF(ISNA(VLOOKUP(A8,ProductList,2,FALSE)),"",
            IF(VLOOKUP(A8,ProductList,2,FALSE)="","",
              VLOOKUP(A8,ProductList,2,FALSE)))