Find previous entry in table with same reference

Question

Hi, I'm trying to work out how I can create this function in Excel. I want Excel to search above in a table for a reference and find the next nearest result e.g.

1 001 Apples

2 002 Banana

3 003 Strawberry

4 001 Apples

5 005 Melon

6 001 Apples

I.e. so that a function in row 6 would look for 001 Apples and find the result in row 4 and not in row 1. Then it would ideally return a value from row 4.

Anyone got any idea how I can achieve that?

Answer 1

Hi Alex,

Hi, I'm trying to work out how I can create this function in Excel. I want Excel to search above in a table for a reference and find the next nearest result e.g.

1 001 Apples

2 002 Banana

3 003 Strawberry

4 001 Apples

5 005 Melon

6 001 Apples

I.e. so that a function in row 6 would look for 001 Apples and find the result in row 4 and not in row 1. Then it would ideally return a value from row 4.

Anyone got any idea how I can achieve that?

Assume the ID's  are in A2:A10, the data to return is in column C, the lookup value is in F1and the instances of the lookup value to be considered are in E3:E8; in F3 enter the array formula:

=IFERROR(INDEX($C$2:$C$10,SMALL(IF($A$2:$A$10=$F$1,ROW($A$2:$A$10)-ROW(INDEX($A$2:$A$10,1,1))+1),E3)),"")

Confirm the formula with the key combination Ctrl+Shift+Enter. Drag the formula down as far as needed to return the values for the 2nd, 3rd, 4th ... values.

You may download my test file Alex20180109.xlsx.

===

Regards,

Norman

Answer 2

For Cell C6 - but what do you want if 001 Apples does not appear above?

Array Enter (enter using Ctrl-Shift-Enter)

=IF(COUNTIFS($A$1:$A5,$A6,$B$1:$B5,$B6)>0,INDEX(C$1:C5,MAX(IF($A$1:$A5=$A6,IF($B$1:$B5=$B6,ROW($B$1:$B5))))),"Not already entered")

Then copy across and down as needed.