Here we are going to demonstrate how to get lookup results on multiple column by entering formula in just a single cell
In the above figure we want the State and Age for Peter from Table range A1:C9.
1st Alternative:
Array function
Method:
Step 1: Highlight both cell starting from G4 till H4, where we need the VLOOKUP formula
Step 2: Enter formula: =VLOOKUP(F4,$A$1:$C$9,{2,3},0)
We are putting this braces because we want to get the value from Table Range column 2 and 3.
Step 3: Press "Control" + "Shift" + "Enter" after entering the formula
Additional braces will appear: {=VLOOKUP(F4,$A$1:$C$9,{2,3},0)}
Note: Array formula cannot be deleted or changed in any cell. For example, if we want to delete the content of cell G4 or H4, it will not allow. The changes can only be done by selecting both the cells, as they both are the part of the same array.
2nd Alternative:
Dynamic Array Function
Method:
For Office 365 versions:
The First 2 steps will remain same. However, in 3rd step the user just need to press Enter. It’s a dynamic array feature
of Excel 365 version.
👇1st and 2nd Alternative method
3rd Alternative:
VLOOKUP with Match
In this alternative we will use Excel “Match” function to
identify the column number.
Match function is used to return lookup value position in a array
in form of an integer.
Match function has 3 components:
Lookup value - The value we want to search for.
Lookup array - The list range, which contains the lookup
value.
Match type: 0 is Exact match, 1 is less than or next smallest and -1 is greater than or next largest
Method:
In Cell G16, formula entered is =VLOOKUP(F16,A1:C9,MATCH(G15:H15,A1:C1,0),0)
Make sure that the headers should match with the Table Array headers
👇3rd Alternative method
4th Alternative:
Filter formula
In Cell G22, formula entered is =FILTER(B1:C9,A1:A9=F22)
Make sure that the headers should match with the Table Array headers
There are lot of other ways too to get lookup results in multiple columns, which will be covered in future tutorials.
👇4th Alternative method
Thank You!!