Search code examples
excelexcel-formula

Always get the first value above the XLOOKUP result (no matter how many rows are in between)

  | A  |        B        | C |    D    |
--|----|-----------------|---|---------|---
1 |    |Brand A          |   | Criteria|
2 |    |Production Line1 |   |   P2    |
3 | P1 |      500        |   |         |
4 | P1 |      100        |   | Result  |
5 | P1 |      800        |   | Brand B |
6 |    |                 |   |         |
7 |    |Brand B          |   |         | 
8 |    |Production Line1 |   |         |
9 | P2 |      700        |   |         |
10| P2 |      300        |   |         |
11|    |                 |   |         |
12|    |Brand C          |   |         |
13|    |Production Line2 |   |         |
14| P3 |      200        |   |         |
15| P3 |      500        |   |         |
16| P3 |      800        |   |         |
17| P3 |      300        |   |         |
18|    |                 |   |         |

In Cell D5 I use this formula to extract the brand based on the criteria in Cell D2:

D5 = OFFSET(XLOOKUP(D2;A1:A18;B1:B18;NA();0);-2;0)

All this works fines

Now it can happen that the user inserts rows between

row 1 and 2 or
row 7 and 8 or
row 12 and 13.

If this happens the formula above does not work anymore because it is fixed to -2.

Do you have any idea if there is a way to make it dynamic so it always works no matter how many rows the user inserts in between?

Solution

  • You've described how users can input rows between rows that describe brand and production line. Therefor it could still be safe to say that the brandname will be two places below that of the previous criteria:

    enter image description here

    Formula in D2:

    =IFERROR(INDEX(B:B,XMATCH("?*",A1:INDEX(A:A,XMATCH(D2,A:A)-1),2,-1)+2),B1)

    Another option is that all rows in B:B from brand down are filled:

    enter image description here

    Formula in D5:

    =INDEX(B:B,XMATCH("",B1:INDEX(B:B,XMATCH(D2,A:A)-1)&"",,-1)+1)