# Excel - Lookup exact match in one column and exact or next higher match in another column

## The problem

I have a three-column lookup table in Excel, like this:

Figure 1. The lookup table.

Given a first input lookup value, typed in cell `F1`, which can be any alphanumeric string of characters and must be exactly equal to any of the values in the first column of the lookup table (W, X, Y, or Z); and given a second input lookup value, typed in cell `F2`, which can be any number and must be between the least and greatest numbers in the second column of the lookup table (1 and 25 respectively). I want to find the corresponding output value in the third column of the lookup table which satisfies the following and is displayed in cell `F3`:

1. The value on the first column of the same row as the output value is equal to the first input value. In other words, the match must be exact for the first column.
2. The value on the second column of the same row as the output value is equal to or immediately greater than the second input value. In other words, the match must be exact or the next higher one for the second column.

Also, I would like the formula to fulfil the following:

1. Doesn't require `Ctrl` + `Shift` + `Enter`.
2. Doesn't require Excel 365, but works for Excel 2019/2016.
3. Doesn't requiere the table values to be in ascending or descending order, but either or none is fine.

## Examples

First example. Suppose the input lookup values are `Y` (for the first column) and `20` (for the second column), then the output should be `I`, since the row with the output value `I` has `Y` on the first column and `20` on the second column.

Figure 2. First example (exact match for second input lookup value) with expected output.

Second example. Suppose the input lookup values are `X` (for the first column) and `7` (for the second column), then the output should be `E`, since the row with the output value `E` has `X` on the first column and the immediate greater value than `7` on the second column (which is `10`).

Figure 3. Second example (next higher match for second input lookup value) with expected output.

## My attempt

To limit the available values the user can choose for the first and second input lookup values, I can create data-validated cells. I know how to do this, and this is a bit irrelevant; I'm more interested in the formula for looking up the output value. I have the following formula:

``````=INDEX(C2:C13, MATCH(1, INDEX((A2:A13 = F1) * (B2:B13 >= F2),),0))
``````

The formula satisfies the first and second requirements, but not the third: it works when the first and second columns are in ascending order, but not when the second column is in descending order. This is shown in the following figures.

Figure 4. First example with my formula, with both input columns in ascending order. Successful (the output is `I`).

Figure 5. Second example with my formula, with both input columns in ascending order. Successful (the output is `E`).

Figure 6. Second example with my formula, with second input column in descending order. Failed (the output should be `E`).

Without recourse to `O365`, and given your wish to avoid formulas which require `CTRL+SHIFT+ENTER`, this will necessitate a rather lengthy construction, for example:

`=IF(COUNTIFS(A2:A13,F1,B2:B13,">="&F2),INDEX(C2:C13,MATCH(AGGREGATE(15,6,B2:B13/((A2:A13=F1)*(B2:B13>=F2)),1),INDEX(B2:B13/((A2:A13=F1)*(B2:B13>=F2)),),0)),"No Result")`

Although less intelligible, the following is a more concise and less resource-intensive alternative:

`=IF(COUNTIFS(A2:A13,F1,B2:B13,">="&F2),LOOKUP(1,0/FREQUENCY(0,(0.5+(B2:B13-F2))*8^8^(A2:A13<>F1)),C2:C13),"No Result")`

Although note that, unlike the first, this second solution may fail if the entries in `F2` and `B2:B13` are not all integers.

The initial `COUNTIFS` clause is used to first determine whether there are in fact any rows which meet your criteria.

You can achieve this with a formula like the following. (In it I have regular cell addresses, rather than Table references. Something to substitute for as appropriate.)

``````=XLOOKUP(F2,  INDEX(  FILTER(A2:C13,A2:A13=F1), , 2),  INDEX(  FILTER(A2:C13,A2:A13=F1), , 3),,  1)
``````

The `FILTER` does the collecting of 1st lookup terms and is set to be exact. Then the `XLOOKUP` handles the "match or next larger" lookup using the results of the `FILTER` portion. `INDEX` is used in the middle, so to speak, wrapping the `FILTER` results to give `XLOOKUP` the columns of data to be looked through.

However, there are other strategies for the step performed by `INDEX`. For instance, one can do a `FILTER` on the data but only return the desired column. Probably better as it frees one from having to allow for the potentially changing Table structure. I set it up the first way 'cause you have a Table and one doesn't think first about them changing structure, but... they can. So probably better is:

``````=XLOOKUP(F2,  FILTER(B2:B13,A2:A13=F1),  FILTER(C2:C13,A2:A13=F1),,  1)
``````