| 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 want to extract the brand based on the criteria in Cell D2.
So far I have been able to implement this XLOOKUP:
=XLOOKUP(D2,A1:A18,B1:B18,NA(),0)
The result of this XLOOKUP is 700.
However, I need to modify the XLOOKUP so it always takes the value in the cell two rows above the results.
In the example Cell B7 (Brand B)
Do you have any idea how to do this?
>Solution :
=OFFSET(XLOOKUP(D2,A1:A18,B1:B18,NA(),0),-2,0)