Help with Index/Match Function

[Nordicrx8]

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!

	OPM	EOW	OPW
NO RCY
CRY	$0.68	$1.49	$2.97
RBN	$0.14	$0.30	$0.59
32Y	$0.27	$0.59	$1.19
64Y	$0.41	$0.89	$1.78
96Y	$0.68	$1.49	$2.97

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Tom Urtis]

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:

=INDEX(Sheet1!A1:D7,MATCH(W1,Sheet1!A1:A7,0),MATCH(X1,Sheet1!A1:D1,0))

 

[Nordicrx8]

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!!

 

[Tom Urtis]

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))

 

[Marcelo Branco]

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.

 

[Tom Urtis]

Yes indeed, thanks Marcelo. I did not insert it when copying & translating Nordicrx8's formula where it was also missing.

 

[Nordicrx8]

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!