asked on

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):

**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

Suppose you have a spreadsheet which contains a 3-column table.

For example, the table in the following screenshots contains three ways (

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):

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

Thanks

Microsoft Excel