Vlookup of three columns to pull a single record

Filed in Excel on Jan.10, 2010.

Question: Does anyone know how to do a vlookup of three columns to pull a single record?

Answer: I am guessing that you want to lookup a value in any of three columns to pull a single record. In this example I search column 1,2 and 4. The search result is pulled out of column 5.

Array formula in B17 using named ranges:

=INDEX(tbl, SMALL(IF(COUNTIF(search_values, INDEX(tbl, , 1, 1))+COUNTIF(search_values, INDEX(tbl, , 2, 1))+COUNTIF(search_values, INDEX(tbl, , 4, 1)), ROW(tbl)-MIN(ROW(tbl))+1, ""), ROW(A1)), 5) + CTRL + SHIFT + ENTER copied down as far as needed.

Array formula in B17 using cell references:

=INDEX($C$5:$H$10, SMALL(IF(COUNTIF($B$13, $C$5:$C$10) +COUNTIF($B$13, $D$5:$D$10) +COUNTIF($B$13, $F$5:$F$10), ROW($C$5:$H$10)-MIN(ROW($C$5:$H$10))+1, ""), ROW(A1)), 5) + CTRL + SHIFT + ENTER copied down as far as needed.

The bolded numbers in the above formulas are column numbers. See column numbers in the picture above.

Above formulas can also pull multiple records.

Named ranges

tbl ($C$5:$H$10)
search_values (B13)
What is named ranges?

How to increase the number of search strings

Change the named range search_values.

Download excel example file

vlookup of three columns to pull a single record.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

SMALL(array,k) returns the k-th smallest row number in this data set.

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

ROW(reference) returns the rownumber of a reference

Share and Enjoy: