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 i...

Formulas and Functions

HansVogelaar
Sep 19, 2022
Helveticathebold

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.

HansVogelaar

MVP

Sep 19, 2022

Helveticathebold

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.

Helveticathebold

Copper Contributor

Sep 19, 2022

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!

Forum Discussion

checking a price list function help - if??

Resources