Search code examples
google-sheetsgoogle-sheets-formulaminarray-formulasgs-vlookup

Getting the lowest price in Google Sheets

I have 2 sheets that are set up like this:

  • Sheet A is a log with 2 columns, (Item Code, Price). Item codes are not unique and there may be multiple rows with similar item codes (i.e., Item 1 was sold at $1 today and it was sold at $0.5 last week)

  • Sheet B is sort of a lookup sheet (with similar columns to Sheet A) whereupon filling the item code, the price column will automatically be filled with the lowest price of that item code from Sheet A

I've done some searching and figured that to get the values to be filled in automatically in Sheet B, a vlookup would be sufficient. However, vlookup only returns the value of the first instance of the index.

Is anyone able to guide me on how I might be able to get the lowest price from Sheet A to show on Sheet B?

Solution

  • you can use your VLOOKUP fx with sorted 2nd parameter:

    =ARRAYFORMULA(IFNA(VLOOKUP(A1:A, SORT('Sheet A'!A:B, 2, 1), 2, 0)))