lookup value and show last cell not blank

[tonnerre2000]

i have a value in A1, I want to find that value in column B:B, then find the last non blank cell in that row.
A B C D E F G
1 Apple
2 Pear
3 Banana
4 Apple 1 2 Empty cell 4 empty cell
5 cherry
6
7

I would like to have the value 4 in the cell with the function.

Thank you very much

[alansidman]

Your explanation is not quite clear. Perhaps if you posted a sample spreadsheet and showed the expected results in the spreadsheet it would be helpful. Your visual does not make any sense since you indicated you are looking in column B for a value that is in column A. I don't see any correlation.