-1

Currently, I have a single sheet which contains: ID, ProductName, Price

I need help with the formula so that in another tab (Google Sheets) the way to get a price based on ID(or product name)

if I select for example A1 and will change product ID in the formula, it will display the product price based on this ID (or product name)

player0
  • 124,011
  • 12
  • 67
  • 124
Oksana Ok
  • 515
  • 3
  • 7
  • 19
  • The question here is unclear - are you unaware of how to use `VLOOKUP`, or do you not know if you are given the ProductName or the ID? (Then use `IFERROR` to check one, and then the other - e.g. `IFERROR(VLOOKUP(ID, etc), VLOOKUP(Name, etc))`) – Chronocidal Jun 04 '19 at 09:01
  • @Chronocidal have amended the question – Oksana Ok Jun 04 '19 at 09:02

2 Answers2

0

If you look up VLOOKUP on Microsoft Office Support, you will see that it gives several examples of how to use VLOOKUP for a value.

Example 5 seems the closest to what you want (i.e. "If the ID cannot be found, return something else") - but instead of just saying "Product Not Found", you want to use another VLOOKUP to compare the Product Name instead. To simplify, I will use the IFERROR function:

=IFERROR(VLOOKUP(A1; 'Products And Prices'!$A:$C; 3; FALSE); VLOOKUP(A1; 'Products And Prices'!$B:$C; 2; FALSE))

An important thing here is that final ; FALSE, to ensure that we get an error if the value is not found (instead of just returning the "best fit")

Chronocidal
  • 6,827
  • 1
  • 12
  • 26
0

if you need it for range use this:

=ARRAYFORMULA(IFERROR(IFERROR(VLOOKUP(A1:A; Sheet1!A:C; 3; 0); 
                              VLOOKUP(A1:A; Sheet1!B:C; 2; 0))))
player0
  • 124,011
  • 12
  • 67
  • 124
  • it checks range A1:A for your input. if input is found in column A in sheet1 it returns column C from sheet1. if it doesnt find a value in A column of sheet1 it checks column B in sheet1 and returns column C. if no value is found it returns nothing. in other words in range A1:A of your 2nd sheet you can either input valid values of column A or column B of sheet1 and if found it will return you column C of sheet1 – player0 Jun 04 '19 at 12:21