Help with Index/Match Function

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
144
Office Version
  1. 365
Platform
  1. Windows
Hey All!

I'm getting stuck on how to use an index formula to get the data I'm looking for. Below is the data that I need to index. On another tab, there are two drop downs. One dropdown to select an option in bold, and one to select an option that is underlined below. (This info starts in A1, ends in D7)

I need a formula that will automatically pull the right price based on the user selection. Maybe an Index won't work in this scenario?

Thanks in advance for your help!

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"][/TD]
[TD="class: xl66, width: 64"]OPM [/TD]
[TD="class: xl66, width: 64"]EOW [/TD]
[TD="class: xl66, width: 64"]OPW[/TD]
[/TR]
[TR]
[TD="class: xl66"]NO RCY[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl66"]CRY[/TD]
[TD="class: xl67"]$0.68 [/TD]
[TD="class: xl67"]$1.49 [/TD]
[TD="class: xl67"]$2.97 [/TD]
[/TR]
[TR]
[TD="class: xl66"]RBN[/TD]
[TD="class: xl67"]$0.14 [/TD]
[TD="class: xl67"]$0.30 [/TD]
[TD="class: xl67"]$0.59 [/TD]
[/TR]
[TR]
[TD="class: xl66"]32Y[/TD]
[TD="class: xl67"]$0.27 [/TD]
[TD="class: xl67"]$0.59 [/TD]
[TD="class: xl67"]$1.19 [/TD]
[/TR]
[TR]
[TD="class: xl66"]64Y[/TD]
[TD="class: xl67"]$0.41 [/TD]
[TD="class: xl67"]$0.89 [/TD]
[TD="class: xl67"]$1.78 [/TD]
[/TR]
[TR]
[TD="class: xl66"]96Y[/TD]
[TD="class: xl67"]$0.68 [/TD]
[TD="class: xl67"]$1.49 [/TD]
[TD="class: xl67"]$2.97 [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Assuming...
• Your pictured table resides on Sheet1
• Your drop down list to select a bolded item (those in range A2:A7 on supposed Sheet1) is on the other worksheet's (let's call it Sheet2) cell W1
• Your drop down list to select an unbolded item (those in range B1:D1 on supposed Sheet1) is on the other worksheet's (let's continue to call it Sheet2) cell X1
...then in some other cell on that Sheet2 is this formula:
Code:
=INDEX(Sheet1!A1:D7,MATCH(W1,Sheet1!A1:A7,0),MATCH(X1,Sheet1!A1:D1,0))
 
Upvote 0
That is very close, if not the same formula I have been using. I keep getting "NA" as an error. Here is what I am entering in:

=INDEX(A1:D7,MATCH('Residential '!D8,A1:A7,0),MATCH('Residential '!D9,A1:D1,0))

Any thoughts on why it is Kicking back this error?

Thanks again for your help!!
 
Upvote 0
I know the formula I posted works because I tested it with your data.
Take a close look at your formula which is different than the one I posted.
For example, notice that I have MATCH(W1,Sheet1!A1:A7
but you have MATCH('Residential '!D8,A1:A7
and you said your two drop-down input cells are on a different sheet than the sheet (presumably named Residential) which holds the source table.
Your formula should be the syntax
=INDEX(A1:D7,MATCH(D8,'Residential'!A1:A7,0),MATCH(D9,'Residential'!A1:D1,0))
 
Upvote 0
Hi Tom,

I think your formula has typo.
Shouldn't it be?
=INDEX('Residential'!A1:D7,MATCH(D8,'Residential'!A1:A7,0),MATCH(D9,'Residential'!A1:D1,0))

M.
 
Last edited:
Upvote 0
Yes indeed, thanks Marcelo. I did not insert it when copying & translating Nordicrx8's formula where it was also missing.
 
Last edited:
Upvote 0
Thanks for all your help!! I found the error - this wasn't my sheet to start with. The person who created it originally put a space at the end of all the information above. Since the formula was looking for an exact match, it was kicking back an error due to the space at the end. (And I didn't even think to check it!) I deleted all the spaces, and boom - works! (Boy, was it driving me crazy!)

Thanks again, and sorry for the delayed response - it's been super hectic at work!
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top