# Lookup value in column from bottom to top

1. ## Lookup value in column from bottom to top

Hello!

Does anyone know how to lookup a value in a single column range from the bottom up, and return its position from the bottom up?

I have a column which is filled with values either =1 or ="". I would like to find the position from the bottom where the first value of ="" occur in a range of ten rows (let's say A1:A10).

If there are several places where ="" occur, I want to still only get the position from the bottom where the first ="" appear.

I have tried different combinations of Lookup, vlookup, match and index, but everything seems to either be counting from the top down, or simply return the lookup value ="", or get confused when there are several matches in the range.

Alternatively, I am the one that is confused because I don't know what I'm doing.

Does anyone know how to solve this?

I am using Excel 2019.

2. ## Re: Lookup value in column from bottom to top

What does "position from the bottom" mean? Do you want to return the row number where the last "" occurs (let's say this is on row 7), or the number of rows above the bottom (i.e. 10 - 7 = 3) ?

Pete

3. ## Re: Lookup value in column from bottom to top

By the way, this array* formula will give you the row number where the last "" appears (i.e. closest to the bottom):

=MAX(IF(A1:A10="",ROW(A1:A10)))

*An array formula must be committed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter.

You may need to use a semicolon ( ; ) instead of a comma ( , ) in the formula, depending on your regional settings.

Hope this helps.

Pete

4. ## Re: Lookup value in column from bottom to top

Thank you, it seems to work. I will place this formula next to the bottom cell in the range I search, so I will add =ROW()+1-MAX(IF(AO14:AO23="";ROW(AO14:AO23))).

I hope the array format doesn't cause any problems, but I am not that familiar with these kind of formulas. In this spreadsheet the formula will be repeated close to 30 000 times, and will also contain an INDIRECT() formula to specify the range if needed. I might even need to include an IF() formula to exclude the case where there is no match.

But now I have something to work with! Thank you!

5. ## Re: Lookup value in column from bottom to top

It just returns zero if there is no match.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

6. ## Re: Lookup value in column from bottom to top

Hello again,

It seems this isn't really solved after all. The formula that was provided does work. But it is not practical in the worksheet given that it must be repeated thousands of times, and that it has to be activated by ctrl + shift + Enter in each cell.

I have a data set of about 30 000 rows. This formula will check the ten previous rows as described before, and then the next formula will do the same one row further down and so on.

1) Is there a way to activate several array formulas at once?

2) Are there any alternative formulas to solve the original problem?

7. ## Re: Lookup value in column from bottom to top

Never mind. I think I figured it out, and I will use this formula:

=ROW()-LOOKUP(2;1/(AO14:AO23="");ROW(AO14:AO23))

No arrays, and it seem to work ok.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1