Lookup but ignore blanks

Hi,

On my transactions sheet I have a massive list of product transaction (such as stock movements and orders). In this list, the same product can appear hundreds of times, but not every column has data in it all the time. As an example, when an order is processed, the "stock quantity" column is left blank, since that particular transaction doesn't care how many are in stock, it instead puts a -1 in the "orders" column. My question:

Obviously, if i do a lookup for a particular product it is going to find the first instance where the product code matches the one i'm looking for and return that value and sometimes that will be a blank. I'm currently using a lookup that looks UP the transaction list so that it catches the last and most recent data first...

Please Login or Register  to view this content.

I have no problem in using some function other than the lookup, so long as it isn't going to cause an issue if I have 50+ cells running the same formula when a product code is entered into B28, and the result can be a number or text.

In pseudo-code, my formula would do this:

=
if cell 28 is blank, leave my cell blank otherwise

look from the bottom of the transactions list, column A, for a match to B28 and

when a match is found, check for a value >0 in column F

if the cell is blank, keep moving up the rows until you find a >0 value otherwise

give the value in column F

I hope my description is useful, and thanks in advance for the help I will receive

Thanks for the fast and effective response, this formula works beautifully.

I won't resent it if you don't accept my request but would you mind telling me how this formula works? It's not the first time I've come across the * within a lookup and I just can't think of how it would make the difference?

From what i can tell, the formula look for the number of times that b28 = cells in colA and colF >0, then returns the value in colF. I don't understand how that somehow means it omits the times when colF =<0

This is a chance for me to learn something valuable here, and i'm happy if you can link me to something that can explain the use of * in formulas.

Thanks again for the solution

Thanks for the fast and effective response, this formula works beautifully.

I won't resent it if you don't accept my request but would you mind telling me how this formula works? It's not the first time I've come across the * within a lookup and I just can't think of how it would make the difference?

From what i can tell, the formula look for the number of times that b28 = cells in colA and colF >0, then returns the value in colF. I don't understand how that somehow means it omits the times when colF =<0

This is a chance for me to learn something valuable here, and i'm happy if you can link me to something that can explain the use of * in formulas.

Thanks again for the solution