I have an excel workbook with two sheets. In sheet 1 in column B I have order numbers and I would like to return a percentage in column G of sheet 1. In sheet 2 I also have order numbers in column B and I have percentages in column H. So, it looks like below:
SHEET 1
| Column B | Column G |
|---|---|
| ORDER NR 1 | FORMULA |
| ORDER NR 2 | FORMULA |
SHEET 2
| Column B | Column H |
|---|---|
| ORDER NR 1 | 50% |
| ORDER NR 2 | 20% |
| ORDER NR 1 | 10% |
| ORDER NR 2 | |
| ORDER NR 1 | 100% |
| ORDER NR 2 | 60% |
New entries are added to the bottom of the table on sheet 2 and, hence, order numbers appear multiple times.
Now, I am looking for a formula which gives me the latest percentage/ percentage most bottom for the corresponding order number. E.g., for order number 1 in column G on sheet 1 100% should be returned and for order number 2 60% should be returned.
I tried chatgpt which has given me different options [e.g., =INDEX(sheet2!$H$9:$H$1000000; MATCH(2; 1/(sheet2!$B$9:$B$1000000=B10); 0))] but none of them worked. Thanks in advance!
>Solution :
Formula in B1:
=XLOOKUP(A1:A2,D1:D6,E1:E6,,,-1)