Vlookup of three columns to pull a single record
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:
copied down as far as needed.
Array formula in B17 using cell references:
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
Related posts:
Lookup multiple values in different columns and return a single value
Vlookup with 2 or more lookup criteria and return multiple matches in excel
Identify missing values in two columns using excel formula
Vlookup – Return multiple unique distinct values in excel
Extract largest values from two columns using array formula in excel
Recommended categories
Hi Oscar,
How do i change the font size and color in a combo box ?
Appreciate your help.
Thanks
Haroun
Haroun,
You can only change font size and color in an active x combo box.
Read more: http://www.ozgrid.com/forum/showthread.php?t=73189
hi oscar,
this above solution is very good and very handy. thank you very much. i made a slight update to this for error-suppression that i thought of sharing here:
={LOOKUP(REPT("Z",25), CHOOSE({1,2},"", 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)))}
i do not have access to XL'03 in order to check this, but i hope that by _not_ using XL'07-specific "IFERROR" for error-suppression, this formula may be useful for older versions too. am i correct in that assumption?
the purpose for which i employed this formula, i was able to drop the 'area_num' argument from INDEX. is there a specific situation in which *not* having that would wreak havoc?
as always, much thanks and kind regards for all that you share with us.
K. Yantri
i do not have access to XL'03 in order to check this, but i hope that by _not_ using XL'07-specific "IFERROR" for error-suppression, this formula may be useful for older versions too. am i correct in that assumption?
No, use =IF(ISERROR(formula), errorformula, formula)
Thanks for commenting!