Finding the last column using MATCH

Hi, I have a row with text in it. I would like to find the last column that has the text "up". Sometimes the columns will contain #N/A due to vlookup formula. Could you pls help me with the formula? thanks

File attached.

Does it has to be with MATCH function?
Because with LOOKUP is quite easy.

Formula:

Please Login or Register  to view this content.

Thanks. This works. Could you pls explain why you have "2" and "1/" in the formula? I need to modify it to other parts of the spreadsheet.

As you can see, 2 and 1/ are constants so they don't need to be changed for other parts.
What you change are vectors and criteria.
To understand this first you need to know how LOOKUP formula works.

This is syntax:

Formula:

Please Login or Register  to view this content.

In this case LOOKUP function looks in a one-row or one-column range (known as a vector) for a value (lookup_value) and returns a value from the same position in [result_vector] a second one-row or one-column range.

If the LOOKUP function can't find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value.

If such doesn't exist it will return LAST value in a column.

We are searching for value 2 in array 1/(A1:Z1="up")
(A1:Z1="up") will evaluate TRUE/FALSE values depending are they equal to "up" or not.

In your example it will look like this:

Please Login or Register  to view this content.

1/TRUE is 1 and 1/FALSE is error (same as 1/0).

Therefore you matching number 2 (notice that you can use here ANY number BIGGER than 1) in the array:

Formula:

Please Login or Register  to view this content.

Since it can't find 2 it return last value that is red 1.

If you use this formula result will be 1.
If you add [result_vector] you will get solution at that POSITION.

In this case 10 because (you can follow this by click EVALUATE FORMULA in FORMULA TAB):

Please Login or Register  to view this content.

This will give you shorter version instead of choose.
And it's not limited up to Z column.

Formula:

Please Login or Register  to view this content.