Building a database-to find the data of last row for every row

Find this formula in B10 of the attached and fill down as far as needed.

Formula:

Please Login or Register  to view this content.

Good deal. You are welcome and thank you for the feedback.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

I’ll do my best.

LOOKUP(lookup_value, lookup_vector, [result_vector])
=IFERROR(LOOKUP(2,1/(A10=$A$2:A9),$B$2:B9),"")

Reference cell B10. The heart of the formula is in the lookup_vector.

A10 is compared with all the items in the preceding rows $A$2:A9. If you select (A10=$A$2:A9) in the formula bar and hit the F9 function key you will see {FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}. Any mathematical operation performed on TRUE/FALSE will coerce them into their underlying numeric values 1/0.

Extend the selection in the formula bar to include 1/(A10=$A$2:A9) and hit the F9 function key. You will see the array {#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}. LOOKUP is one of the functions that will ignore errors in the lookup_vector.

LOOKUP will attempt to match lookup_value (2 in this case) to that array and find the right most value less than or equal to 2. It then returns the value from the [result_vector]) ($B$2:B9) corresponding to that match.

As you fill the formula down the ranges $A$2:A9 and $B$2:B9 will increase as the comparison value in A10 will become A11, A12 … etc.

You can also follow along using the ‘Evaluate formula’ feature under FORMULAS.

The F9 and ‘Evaluate feature’ are excellent tools for analyzing, trouble shooting and self instruction.

Does this help?