| 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?
=OFFSET(XLOOKUP(D2,A1:A18,B1:B18,NA(),0),-2,0)