Find the previous non-blank value from a column using a value in an adjacent column as search key [duplicate]

Question

How can i VLOOKUP from a text to the left and if its nothing on the left to keep going up until find a text.

Basically the i want to vlookup the invoice from the model. so if there is no text on the left cell of the model to keep going up until it finds a cell with text and extract that info.

examples in the picture.

so if i want to look for the invoice of the model G10-B the function needs to go one cell left and if there is no text to go up until find some text, in this example goes up until find the cell 2021-C-MI

this is my sample spreadsheets https://docs.google.com/spreadsheets/d/1iefvYAg96rV16c6ARFrd5V5WZKU_j70JZzQn1tD0568/edit?usp=sharing here is the source sheet and here is the search sheet https://docs.google.com/spreadsheets/d/1TvQHDeW8yMJVCeXrzWGYGDMY0wGEDBd_fIzISMZZ-Rw/edit?usp=sharing

Answer 1

I have added two sheets to your SEARCH SHEET spreadsheet.

The first is called IMPORT. This sheet brings over all data from Columns A and B of the INVOICE-MODEL sheet. It is currently Hidden, since it will not be needed for direct reference.

I then duplicated your SEARCH sheet and renamed it Erik Help, just to keep things separated, in case someone else wants to jump in and help.

The formula in 'Erik Help'!B2 references the new IMPORT sheet to find the correct invoice:

=ArrayFormula(IFERROR(VLOOKUP(IFERROR(VLOOKUP(B1;{IMPORT!B3:B\ROW(IMPORT!B3:B)};2;FALSE);0);FILTER({ROW(IMPORT!A3:A)\IMPORT!A3:A};IMPORT!A3:A<>"");2;TRUE);"NOT FOUND"))

Two VLOOKUPs are performed. The innermost VLOOKUP finds the row number of the model number on the IMPORT sheet. Then the outermost VLOOKUP uses this row number to search a FILTERed set from IMPORT!B3:B where Column A is not null/blank. Since rows are always in sequential order, I could use TRUE as the final parameter of this outermost VLOOKUP, which will bump backward to find the last match that is lower than the search.

So, if your model is found in Row 5, the outermost VLOOKUP will search for 5 (which is blank in IMPORT!A:A) and just bounce back to Row 3 (which is not blank in IMPORT!A:A). Once this closest occupied Row number is found, the corresponding invoice from IMPORT!A:A is returned.

You will notice that I also applied Data Validation to cell B1, which is drawing from IMPORT!B3:B. This will assure that you don't accidentally enter a model which does not exist.

Answer 2

Assuming that invoices and models are in A2:B, you can look up the first matching invoice for the model in cell E2 like this:

=arrayformula( 
  iferror( 
    vlookup( 
      E2, 
      { 
        B2:B, 
        iferror( 
          vlookup( 
            row(A2:A), 
            if( len(A2:A), { row(A2:A), A2:A } ), 
            2, 
            true 
          ) 
        ) 
      }, 
      2, 
      false  
    ) 
  ) 
)

To look up the first matching invoice for a whole column of models in E2:E, replace E2 with E2:E in the formula.

To use the formula in a spreadsheet whose locale uses semicolons instead of commas as argument separators, modify the formula like this:

=arrayformula( 
  iferror( 
    vlookup( 
      E2; 
      { 
        B2:B \ 
        iferror( 
          vlookup( 
            row(A2:A); 
            if( len(A2:A); { row(A2:A) \ A2:A } ); 
            2; 
            true 
          ) 
        ) 
      }; 
      2; 
      false  
    ) 
  ) 
)