I have a three-column table in Excel, called Table1, like this:
Given two values (one for each input variable), one which must be exactly equal to any of the numbers in the first column (2, 4, 6, or 8) and which must be typed in cell F2, and another one which can be any number between the least (1) and greatest (25) numbers in the second column and which must be typed in cell F3, I want to find the corresponding value in the third column. If the value typed for the second variable is not present in the second column of the table, then the output value of the next row is chosen.
For example, suppose the lookup values are 4 (for the first column) and 10 (for the second column), then the output should be E, since both 4 and 10 are present in the first and second columns, respectively, and the row with the output E corresponds to those values for the inputs.
Another example. Suppose the lookup values are 8 (for the first column) and 17 (for the second column), then the output should be K; it is not J because the latter corresponds to a value of 15 for the second column, which is strictly less than 17; so the output is K because it corresponds to the value that is immediately after (or greater than) 17, being 20.
My attempt
To limit the available values the user can choose, I could create data-validated cells. For choosing the values in the first column, the data validation would by of type list and be equal to 2, 4, 6, 8; such cell would be F2. Like this:
For choosing the values in the second column, the data validation would be of the type whole number, with minimum value of 1 and maximum value of 25. Like this:
Now the formulas for the lookup. After googling, I found out that performing a look-up task with two input criteria is known as a two-way lookup. Using the INDEX and MATCH functions, I managed to perform the two-way lookup, unfortunately the formula only allows exact matches, so it works fine when the first and second input values are 4 and 10, but not when they're 8 and 17. The formula is the following, and it is in cell F4:
{=INDEX(Table1[Output], MATCH($F$2 & "|" & $F$3, Table1[1st input variable] & "|" & Table1[2nd input variable], 0))}
(The presence of curly braces means that we must enter the formula with Ctrl Shift Enter instead of just Enter.)
Here's a screenshot for the first successful example:
Here's a screenshot for the second failed example:
I tried changing the third parameter of the MATCH function from 0 to 1, but it returns J (which corresponds to 15 in the second column, but 17 < 15) instead of K (which corresponds to 20, since 17 > 20 and 20 is the closest value to 17 that is immediately after it.)
How can I achieve what I want?
CodePudding user response：
if you have Excel 365 then you can use the new Filter-function:
=INDEX(FILTER(Table1[output],(Table1[1st Input variable]=first)*(Table1[2nd input variable]>=second),"no result"),1)
I named F3 "first" and F4 "second".
FILTER returns all output values where
- column A = value from F3
- column B >= the value from F4.
INDEX selects the first row of the FILTER-result
CodePudding user response：
Not the best way, but you can round the second input to what you need. In your example, all your values are multiples of 5. Just create an exception for number 1 with an IF.
Here's what I tried:
={INDEX($C$1:$C$12;MATCH(F7&IF(ROUNDUP(G7/5;0)=1;1;ROUNDUP(G7/5;0)*5);$A$1:$A$12&$B$1:$B$12;0))}
Notice it's an array formula.