Function Problems

Look Up a Price in Excel by Item Number and Drop-Down Selection

Steve KSteve K Nov 18, 2026 868 views

Question details

Return a price using an item number and a Roanoke or Lineage drop-down selection.

Look Up a Price in Excel by Item Number and Drop-Down Selection
Product
Microsoft Excel
Device & OS
not provided
Scenario
An item number and a price-list selection are entered on one worksheet, while item numbers and prices are stored on another worksheet.
Observed behavior
The desired result is the price at the intersection of the matching item row and the selected price column.
Before you start

Check that Sheet2 contains item numbers in B2:B6, price-list headers in T1:U1, and corresponding prices in T2:U6. Make sure the drop-down values match the headers and that item numbers use consistent text or number formats.

Solution 1Recommended

Use INDEX and MATCH to Return the Selected Price

Use this formula when the item number is in D21 and the Roanoke or Lineage selection is in D29.

The first MATCH finds the item row, and the second MATCH finds the selected price column. INDEX returns the value where they intersect.

1
Check the lookup cells

On the entry worksheet, enter an item number in D21 and select Roanoke or Lineage from the drop-down in D29.

2
Enter the formula

Select the cell where you want the price to appear and enter =IFERROR(INDEX(Sheet2!T2:U6,MATCH(D21,Sheet2!B2:B6,0),MATCH(D29,Sheet2!T1:U1,0)),"NoPrice").

3
Test both selections

Press Enter, then change the selection in D29 to check that the formula returns the corresponding price for the item in D21.

4
Adjust the ranges if needed

Edit the formula in the formula bar if your table is larger or uses different cells. Keep the item range aligned with the price rows and the header range aligned with the price columns.

Use INDEX and MATCH to Return the Selected Price
If NoPrice appears: IFERROR returns NoPrice for any formula error. Check that D21 matches an item in Sheet2!B2:B6 and D29 matches a header in Sheet2!T1:U1, then verify the sheet name and ranges.
WPS Spreadsheets

Look Up Prices by Item and Selection in WPS Spreadsheets

Use INDEX, MATCH, and IFERROR in WPS Spreadsheets to return a price from a table based on an item number and a drop-down selection.

  1. 1. Open the workbook: Open WPS Spreadsheets and select Open to load your workbook.
  2. 2. Check the source table: Select the Sheet2 tab and verify the item numbers in B2:B6, headers in T1:U1, and prices in T2:U6.
  3. 3. Enter the lookup values: On the entry worksheet, type an item number in D21 and choose Roanoke or Lineage from the drop-down in D29.
  4. 4. Enter and test the formula: Select the result cell and enter =IFERROR(INDEX(Sheet2!T2:U6,MATCH(D21,Sheet2!B2:B6,0),MATCH(D29,Sheet2!T1:U1,0)),"NoPrice"). Press Enter and change the drop-down selection to test the result.
Look up prices using both an item number and a price-list selectionOpen and edit Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and worksheet tabsWork with spreadsheets in the free WPS Office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula return NoPrice?

NoPrice appears when the formula encounters an error. Check that D21 matches an item number in Sheet2!B2:B6 and that D29 matches a header in Sheet2!T1:U1. Also check the sheet name and range references.

Can I use a larger price table?

Yes. Edit the ranges in the formula bar, such as changing B2:B6 to B2:B100 and T2:U6 to T2:U100. The item and price ranges must cover the same rows.

How do I create the Roanoke and Lineage drop-down in Excel?

Select D29, choose Data > Data Validation, and set Allow to List. Enter Roanoke,Lineage as the source, or select cells containing those names.

What happens if an item number appears more than once?

MATCH with 0 returns the position of the first exact match. Check Sheet2 column B for duplicates if each item should have only one price row.