Search code examples
excelexcel-formula

Find most recent value/ value at the bottom of column based on criteria

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

  • enter image description here

    Formula in B1:

    =XLOOKUP(A1:A2,D1:D6,E1:E6,,,-1)