# Index/Match function - lookup cell is formula, want it to look for result of formula

1. ## Index/Match function - lookup cell is formula, want it to look for result of formula

I am trying to get the MATCH function to look up the result of the formula in a select cell, not the formula itself.

=INDEX('Daily Ratios'!B4:H35,MATCH('1'!A5,'Daily Ratios'!A4:A35,0),MATCH('1'!E1,'Daily Ratios'!B3:H3,0))

Where the '1'!A5 cell contains +G1 and the result is the day of the week, such as Monday.

I want it to match to "Monday" not the +G1. The function works if I type Monday in the cell, so I know the function is working, but how do I get it to "see" Monday and not the +G1?

Typing in all the days on each of the sheets will take forever and each month it will change, so I'm trying to get it automated so I don't have to do this.  Register To Reply

2. ## Re: Index/Match function - lookup cell is formula, want it to look for result of formula

Hi - can you please attach your file so we can take a look?  Register To Reply

3. ## Re: Index/Match function - lookup cell is formula, want it to look for result of formula

Attached is a partial sample of my workbook.
Tabs 1 and 2 represent the 1st and 2nd of the month.

Original post:
I am trying to get the MATCH function to look up the result of the formula in a select cell, not the formula itself.

=INDEX('Daily Ratios'!B4:H35,MATCH('1'!A5,'Daily Ratios'!A4:A35,0),MATCH('1'!E1,'Daily Ratios'!B3:H3,0))

Where the '1'!A5 cell contains +G1 and the result is the day of the week, such as Monday.

I want it to match to "Monday" not the +G1. The function works if I type Monday in the cell, so I know the function is working, but how do I get it to "see" Monday and not the +G1?

Typing in all the days on each of the sheets will take forever and each month it will change, so I'm trying to get it automated so I don't have to do this.  Register To Reply

4. ## Re: Index/Match function - lookup cell is formula, want it to look for result of formula

Change E1 to:

=TEXT(G1,"dddd")

Change B5 to:

=INDEX('Daily Ratios'!\$B\$4:\$H\$35,MATCH('1'!A5,'Daily Ratios'!\$A\$4:\$A\$35,0),MATCH('1'!\$E\$1,'Daily Ratios'!\$B\$3:\$H\$3,0))

and drag the formula down.  Register To Reply

5. ## Re: Index/Match function - lookup cell is formula, want it to look for result of formula

Try

in B5

=INDEX('Daily Ratios'!B4:H35,MATCH('1'!A5,'Daily Ratios'!A4:A35,0),MATCH(TEXT('1'!E1,"dddd"),'Daily Ratios'!B3:H3,0))

You are comparing a text field "Monday" with date field in E1. you need to compare TEXT with TEXT

it is E1 that contains G1 not A5  Register To Reply

6. ## Re: Index/Match function - lookup cell is formula, want it to look for result of formula

Thank you Glenn and John!! Both of those worked!! Much appreciated for your assistance!!  Register To Reply

7. ## Re: Index/Match function - lookup cell is formula, want it to look for result of formula

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 