Mastering Google Sheets: How To Fetch And Compare Product Prices With VLOOKUP And MATCH

Mastering Google Sheets: How to Fetch and Compare Product Prices with VLOOKUP and MATCH

You run a coffee shop and are looking for a formula for a spreadsheet that can quickly obtain the costs of the items your customers purchase. You’re in luck since I know just how to speed up this procedure! Your pricing matrix is nicely set up in a Google Sheet, with the names of the beverages in one column and the quantities for which each price is charged in the following columns.

The plan is to use the MATCH feature to your advantage when a consumer selects their beverage and cup size. This clever feature enables you to locate the exact spot in the price table that corresponds with the selected drink and amount. After that step down, use the INDEX function to focus on the real price.

Mastering Google Sheets: How To Fetch And Compare Product Prices With VLOOKUP And MATCH

The range of cells from B2 to B11 in your Starbuck Coffee example house the assortment of coffee pricing. The customer’s preferred beverage (let’s say a Caffè Mocha) is conveniently located in cell G3. Watch this MATCH feature in action as it reveals the location of the selected beverage within the overall list:

=MATCH(G3, $B$2:$B$11, 0)

Now observe that the third argument of the MATCH function is fixed at 0, indicating your desire for a perfect match and taking into account the fact that the order of the pricing list is unspecified.

But wait, we’re not finished yet! Use a MATCH function of a similar nature to determine the relative location of the column containing the price associated with the selected cup size. Imagine cells C2 through E2 with the cup sizes properly aligned. The chosen cup size is placed in cell H3 with ease. It’s time to use the MATCH feature:

=MATCH(H3, $B$2:$E$2, 0)

Let’s reveal the INDEX function now that the row and column locations have been established. Watch its strength as it retrieves the true cost from the table:

=INDEX($B$2:$E$11, H5, H7)
Use Vlookup with ArrayFormula and Match:

Now, this is when things really start to heat up. Imagine that you have a customer who has ordered a variety of drinks, each in its own row. Not only are you looking for individual beverage pricing, but also the overall order total. Array Formulas come to the rescue in this situation! Since we want to apply the identical formula to all the rows in your sheet, this is extremely helpful.

There is a small storyline twist, though. Our Array Formulas can’t use the INDEX function we rely on since it can’t yet handle multiple values. But don’t worry! A VLOOKUP function, working in unison with the MATCH function to complete a two-pronged lookup goal, serves as a good substitute.

The user-friendly VLOOKUP function structure is as follows:

=VLOOKUP(
  Item to search for (beverage name),
  Where to search for it (price table range),
  Column number containing the matched value (selected cup size),
  Should it be an exact match? (True or False)
)

This dynamic function searches the defined range (let’s say, B2:E11) for the beverage name, then selects the value in the selected column from the matching row. We set the fourth option to FALSE since the price range is not sorted.

Let’s now focus on how the MATCH function fits into all of this. Its job is to locate the column that lists the beverage’s price for the specified quantity:

=MATCH(
  The query (cup size),
  Where to search (cup size header range),
  0 for an exact match (default is 1)
)

Just a heads-up: the formula may display a #N/A error if a row does not contain the beverage name. We’re enclosing the value with an IFNA function to protect your masterpiece from such problems.

Without further ado, enjoy the result of this magnificent project:

=ARRAYFORMULA(IFNA(VLOOKUP(B14:B, $B$2:$E$11, MATCH(C14:C, $B$2:$E$2, 0), FALSE)))

Mastering Google Sheets: How To Fetch And Compare Product Prices With VLOOKUP And MATCH

There you have it, then! An elegant answer to the pricing conundrums at your coffee shop using MATCH, VLOOKUP, INDEX, and a little bit of Array Formulas for good measure. You are now offering more than simply coffee; you are also delivering efficiency and accuracy. Time to enjoy your perfectly priced drinks in all their magnificence!

Leave a Comment