Forum Discussion
checking a price list function help - if??
I don't even know where to begin.
I have been given a spreadsheet where sheet 1 has a price list with a long number of product codes. Product codes are in column A. The price of each product is in column G.
On Sheet 2 I need to type the product code in a column. It would be brilliant if I could type the product code and then the price was automatically populated in the next column.
Does anyone have any idea what I could use? I'm such an excel beginner and I can't figure out what formula to use. Thanks!
Let's say you enter the product code in cell A2 on Sheet 2 (or select it from a Data Validation drop down list).
If you have Microsoft 365 or Office 2021, enter this formula in B2:
=XLOOKUP(A2, 'Sheet 1'!$A$2:$A$1000, 'Sheet 1'!$G$2:$G$1000, "")
If you have an older version:
=IFERROR(VLOOKUP(A2, 'Sheet 1'!$A$2:$G$1000, 7, FALSE), "")
Change Sheet 1 to the real name of that sheet.
If the price list extends below row 1000, adjust the range(s) in the formula accordingly.
2 Replies
Let's say you enter the product code in cell A2 on Sheet 2 (or select it from a Data Validation drop down list).
If you have Microsoft 365 or Office 2021, enter this formula in B2:
=XLOOKUP(A2, 'Sheet 1'!$A$2:$A$1000, 'Sheet 1'!$G$2:$G$1000, "")
If you have an older version:
=IFERROR(VLOOKUP(A2, 'Sheet 1'!$A$2:$G$1000, 7, FALSE), "")
Change Sheet 1 to the real name of that sheet.
If the price list extends below row 1000, adjust the range(s) in the formula accordingly.
- I can't even begin to thank you for such a quick response and for such a BRILLIANT response. Honestly, this has shaved DAYS off my up coming work load. I knew there must be a faster way to do it but I had no idea how to do it. Thank you!
