# Help with Index/Match Function

#### Nordicrx8

##### New Member
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?

 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>

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Tom Urtis

##### MrExcel MVP
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))``

#### Nordicrx8

##### New Member
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?

#### Tom Urtis

##### MrExcel MVP
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

##### MrExcel MVP

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:

#### Tom Urtis

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

Last edited:

#### Nordicrx8

##### New Member
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!

Replies
3
Views
1K
Replies
9
Views
351
Replies
18
Views
2K
Replies
3
Views
233
Replies
3
Views
174