Sheets: finding the column number of the right-most (highest column) appearance of a specific value in a partially-empty range

Question

I'm making a google sheets function in which i, for a given value(a String) in sheet A, need to fetch the column number(or the letter, either works) of the right-most cell with the given value, in sheet B, given a large range of cells which aren't all necessarily filled with values.

I've tried making some sort of iterative HLOOKUP, but to no avail.

example of a range:

a | b | c | _ |
_  |  _ | e | _  |
e | b |   _ | _   |
y |   _ | b | e |
a | c | _   | _   |

Here, the rightmost appearance of e is on the 4th column, while b = 3, c = 3, y = 1, a = 1. You get the idea. Any help is very very much appreciated.

Edit - 02/03: Clarification for MattKing

I need to fetch the column index of the right-most cell containing a specific value, in a range. When i say right-most, i mean the highest column (ie. column B is higher than column A).

Answer 1

Hope I didn't offend with the X-y problem thing. it just seems like an odd thing to need without any more context. Hope this helps.

Here is a sample sheet with this formula in cell C2 on the tab called 'furthest right':

=ARRAYFORMULA(MAX(IF(data!A:W=B2,COLUMN(data!A:W),)))