Help with Index/Match Function

Nordicrx8

New Member
Joined
Dec 10, 2015
Messages
47
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>
 

Some videos you may like

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
Joined
Feb 10, 2002
Messages
11,208
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
Joined
Dec 10, 2015
Messages
47
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,208
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
Joined
Aug 23, 2010
Messages
16,397

ADVERTISEMENT

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
Joined
Feb 10, 2002
Messages
11,208
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
Joined
Dec 10, 2015
Messages
47
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,326
Messages
5,547,268
Members
410,783
Latest member
sonnny
Top