Day 5: VLOOKUP & HLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). lookup_value: the value to search for. table_array: the range to search in — the lookup column must be the leftmost column. col_index_num: which column of table_array to return (1 = first column, 2 = second, etc.). range_lookup: FALSE (or 0) for exact match; TRUE (or 1) for approximate match — use FALSE for data analysis lookups, TRUE only for range lookups like tax brackets.

#N/A means the lookup value was not found in the leftmost column of the table. Common causes: (1) Extra spaces — "Delhi " vs "Delhi" — fix with TRIM: =VLOOKUP(TRIM(A2),table,2,FALSE). (2) Different data types — number vs text version of same value — fix by converting to same type. (3) Lookup value genuinely does not exist in the table. Wrap with IFERROR to handle gracefully: =IFERROR(VLOOKUP(A2,table,2,FALSE),"Not Found").

VLOOKUP can only return values from columns to the RIGHT of the lookup column. The lookup column must always be the leftmost column of the table_array. If you need to return a value from a column to the left of your lookup column, you must either restructure the data or use INDEX-MATCH, which has no direction restriction.

HLOOKUP (Horizontal Lookup) works like VLOOKUP but searches across rows instead of columns — the lookup value must be in the top row of the range, and you specify which row to return from. Use it when your lookup table is arranged horizontally (headers in a row, data below). In practice, HLOOKUP is rare — most data is arranged vertically. INDEX-MATCH handles both horizontal and vertical lookups.

VLOOKUP only supports one lookup column natively. For two-criteria lookup, create a helper column that concatenates the two keys: =A2&"|"&B2 in a helper column, then VLOOKUP on that concatenated value against a similar concatenated column in your lookup table. Alternatively, use SUMPRODUCT: =SUMPRODUCT((A2:A100=F2)*(B2:B100=G2)*C2:C100) — this works for numeric return values. The cleanest solution is INDEX-MATCH with multiple criteria.