asked on
Using =VLOOKUP() in Excel when the lookup_value can be in column 1 or column 2 of the table_array
Hello,
Suppose you have a spreadsheet which contains a 3-column table.
What formula would enable a user to enter a value from either the 1st or 2nd column but always return the values found in the 2nd and 3rd columns of the corresponding row?
For example, the table in the following screenshots contains three ways (written in columns 1 & 2 and numeric in column 3) to designate months. Also included is an input box (yellow) in which a user can enter a given month — but only in one of the written forms from the 1st (Fig. 1) or 2nd (Fig. 2) columns of the table (ie numeric entries are not allowed):
![Fig. 1]()
![Fig. 2]()
What formula would return the result shown in the blue box regardless of which of the two written forms was utilized by the user?
Notes:
1) the use of months in this example is arbitrary (ie please avoid solutions which include date or time functions).
2) Please disregard the fact that each of the 2nd-column values in the table not only contains exactly three letters but the first three letters of the corresponding 1st-column values. That is just a coincidence of this example but does not apply where I plan to use the solution.
The best approach I've found so far involves naming two defined ranges:
• "Months3" which includes all three columns (Fig. 3) and
![Fig. 3]()
• "Months2" which includes the 2nd & 3rd columns but not the 1st column (Fig. 4):
![Fig. 4]()
With the named ranges and utilizing the =VLOOKUP() function, two separate formulas can be written, one for inputs from the 1st column (Fig. 5):
![Fig. 5]()
and one for inputs from the 2nd column (Fig. 6):
![Fig. 6]()
Note: the function =GET.CELL(6,F8) enables the display (red font) of the adjacent formulas in F8 & F9.
I believe this approach is usable but as can be seen in Fig. 6, each formula works for entries from one of the first two columns but returns #N/A for the other. Therefore, if someone can show me how to nest the two =VLOOKUP() formulas into one so that the 1st column is searched first and the 2nd column is searched only if the 1st column search fails, I think that will do it.
Alternatively, I am interested in learning different but simpler ways to arrive at the same endpoint.
Thanks
Suppose you have a spreadsheet which contains a 3-column table.
What formula would enable a user to enter a value from either the 1st or 2nd column but always return the values found in the 2nd and 3rd columns of the corresponding row?
For example, the table in the following screenshots contains three ways (written in columns 1 & 2 and numeric in column 3) to designate months. Also included is an input box (yellow) in which a user can enter a given month — but only in one of the written forms from the 1st (Fig. 1) or 2nd (Fig. 2) columns of the table (ie numeric entries are not allowed):
What formula would return the result shown in the blue box regardless of which of the two written forms was utilized by the user?Notes:
1) the use of months in this example is arbitrary (ie please avoid solutions which include date or time functions).
2) Please disregard the fact that each of the 2nd-column values in the table not only contains exactly three letters but the first three letters of the corresponding 1st-column values. That is just a coincidence of this example but does not apply where I plan to use the solution.
The best approach I've found so far involves naming two defined ranges:
• "Months3" which includes all three columns (Fig. 3) and
• "Months2" which includes the 2nd & 3rd columns but not the 1st column (Fig. 4):
With the named ranges and utilizing the =VLOOKUP() function, two separate formulas can be written, one for inputs from the 1st column (Fig. 5):
and one for inputs from the 2nd column (Fig. 6):
Note: the function =GET.CELL(6,F8) enables the display (red font) of the adjacent formulas in F8 & F9.I believe this approach is usable but as can be seen in Fig. 6, each formula works for entries from one of the first two columns but returns #N/A for the other. Therefore, if someone can show me how to nest the two =VLOOKUP() formulas into one so that the 1st column is searched first and the 2nd column is searched only if the 1st column search fails, I think that will do it.
Alternatively, I am interested in learning different but simpler ways to arrive at the same endpoint.
Thanks
Microsoft Excel
Last Comment
WeThotUWasAToad