Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupimportrange

How to VLOOKUP from bottom with IMPORTRANGE

Main Sheet: https://docs.google.com/spreadsheets/d/1j71MW_rgbWFxaldl4j4Ww3a22QMRZpy_u2NFrI1jjak/edit?usp=sharing

Formula in C155

IMPORTRANGE Sheet: https://docs.google.com/spreadsheets/d/1ADudSHKhcuPtoep-S8IDXuHXrid4T6RTRrJQT5htZcE/edit?usp=sharing

I found a link with info to reverse the table for VLOOKUP from the bottom (https://infoinspired.com/google-docs/spreadsheet/vlookup-from-bottom-to-top-in-google-docs-sheets/), reason being I have multiple values of the same. I always want to pull the latest value, which is at the bottom.

Current Formula: 

=IFERROR(IF(C149 <= TODAY(),VLOOKUP(VALUE(C149),IMPORTRANGE("1g7AtubscrhkP2y6A0Dk7JUXiqxPhkSwKWlpeG7RVBKI","'South Loop Sales Log'!$A:$K"),2,FALSE),""),"")

Date Values are in column A for IMPORTRANGE. Is there any way to implement this VLOOKUP formula to work with IMPORTRANGE also?

Solution

  • try:

    =IFERROR(IF(C$149 <= TODAY(), VLOOKUP(VALUE(C$149), ARRAY_CONSTRAIN(SORT({
 QUERY(IMPORTRANGE("1ADudSHKhcuPtoep-S8IDXuHXrid4T6RTRrJQT5htZcE", 
 "South Loop Sales Log!A3:K"), "where Col1 is not null", 0), 
 ROW(INDIRECT("A1:A"&COUNTA(IMPORTRANGE("1ADudSHKhcuPtoep-S8IDXuHXrid4T6RTRrJQT5htZcE",
 "South Loop Sales Log!A3:A"))))}, 12, 0), 99^99, 11), ROW(A2), 0), ), )

    0