Getting the lowest price in Google Sheets

1
votes

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?

3
google-sheetsgoogle-sheets-formulaminarray-formulasgs-vlookup
MINIFS doc ref - Tedinoz

3 Answers

1
votes

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

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

You can use MINIFS to find minimum value for a given Item code. MINIFS, MAXIFS and SUMIFS are used to find single value from a list of values under some specific conditions. Formula would be similar to this:

= MINIFS(Sheet$1A2:Sheet$1A5, Sheet$1B2:Sheet$1B5, "=ItemCode")

Find more on this here, link

0
votes

Use Query like this:

=query(filter(SheetA!A2:B,A2:A<>""),"Select Col1, Min(Col2) Group By Col1 Label Min(Col2) ''")

or 

=ifna({UNIQUE( SheetA!A2:A ), ARRAYFORMULA( vlookup( UNIQUE( SheetA!A2:A ), sort( SheetA!A2:B, 1, true, 2, true ), 2, 0 ))})