How to Use an Alphanumeric Mix in VLOOKUP Statements in Excel
=VLOOKUP() is one of Excel's database-like functions and uses the format of =VLOOKUP (Lookup Value, Range of Cells, Column) with an optional range match argument. =VLOOKUP() can accept named ranges for the source of the look-up value, and for the range of cells to be searched, but it cannot take a non-numeric input for the column argument without using another function, like =MATCH().
Other People Are Reading
Instructions
-
-
1
Launch Excel, and create a new worksheet.
-
2
Create a 12-cell data range to be sorted through. Start on Row 2, type "Buffy," "Giles," "Willow" and "Xander" in Column A.
In Column B, list "Blonde," "Ginger," "Red" and "Brown."
In Column C, list "Chocolate," "Mocha," "Raspberry" and "Vanilla."
-
-
3
Type "Name," "Hair" and "Ice Cream" in Cell A1, Cell B1 and Cell C1, respectively. These are your header rows.
-
4
Enter the following formula in Cell D10: "=VLOOKUP(A2,A1:C5,MATCH("Ice Cream",A1:C1),FALSE)". Make sure that the quotation marks are around the words "Ice Cream." Press "Enter." The word "Chocolate" will appear.
-
5
Enter the following formula in Cell C10: "=VLOOKUP(A2,A1:C5,MATCH("Hair",A1:C1),FALSE)". Make sure that the quotation marks are around the words "Hair." Press "Enter." The word "Blonde" will appear.
-
1
Tips & Warnings
What's going on here is that the =MATCH() function is returning the numerical position of the term ("Hair" or "Ice Cream"). This is being used by the VLOOKUP as the column reference. This technique can be flexible. For example, if you replaced the word "Hair" with a cell reference, you could enter the header name dynamically (or make a drop-down list with data validation) to allow you to use one formula that could pull data from any column reference.
