combining 'indirect' function with 'index/match"

**wongth7** · 05-20-2015, 04:57 AM

Hi guys,

i need some help here integrating 'indirect' function with 'index/match' function.
i have an excel spreadsheet which contains several tabs, which named as (CPU 95thP, 201501, 201502, 201503, 201504 etc)

on my main tab, as below, which im trying to type the "indirect, index/match" formula to pull the data from 201501, 201502, 201503 etc
(pls note that the date in row 1 is in custom date format)
[ATTACH][/ATTACH]

while for the date sheet, the data look like this, (as below), im trying to get the data from column C

[ATTACH][/ATTACH]

this is the formula i have typed out so far, but it's is badly not working..lol

Please Login or Register  to view this content.

can someone pls help?

spreadsheet attached
sdfsdfsdf.xlsx

**teylyn** · 05-20-2015, 05:12 AM

Hello,

the screenshots are a good start. The actual workbook would be better.

"badly not working" is one description. Can you embellish that a bit? What does the formula return? What error code?

Are you aware of the Formula Evaluation tool? You can use it to step through a formula and see intermediate results, so you can see which part of the formula needs attention.

So, to get a handle on this, please post the workbook.

cheers, teylyn

**[email protected]** · 05-20-2015, 05:19 AM

please attached excel sheet.
Looks like you have not given column value for Index formula. Which will be just before last bracket.

Regards,
Suhas

**wongth7** · 05-20-2015, 05:45 AM

Originally Posted by teylyn

Hello,

the screenshots are a good start. The actual workbook would be better.

"badly not working" is one description. Can you embellish that a bit? What does the formula return? What error code?

Are you aware of the Formula Evaluation tool? You can use it to step through a formula and see intermediate results, so you can see which part of the formula needs attention.

So, to get a handle on this, please post the workbook.

cheers, teylyn

Hi teylyn, i attached the workbook on my first post...the error i got was #ref something like that, i'm aware of the formula evaluation tool, but still not too sure how to fix it though

**wongth7** · 05-20-2015, 05:46 AM

Originally Posted by [email protected]

please attached excel sheet.
Looks like you have not given column value for Index formula. Which will be just before last bracket.

Regards,
Suhas

workbook attached

**[email protected]** · 05-20-2015, 06:07 AM

Use this formula and make necessary changes while pasting somewhere. text formula and match function reference was wrong.

=INDEX(INDIRECT(TEXT(B1,"yyyymm")&"!$C3:C8326"),MATCH('CPU 95thP'!$A2,INDIRECT(TEXT(B1,"yyyymm")&"!$A3:A8326"),0),1)

Regards,
Suhas

**wongth7** · 05-20-2015, 09:38 AM

Originally Posted by [email protected]

Use this formula and make necessary changes while pasting somewhere. text formula and match function reference was wrong.

=INDEX(INDIRECT(TEXT(B1,"yyyymm")&"!$C3:C8326"),MATCH('CPU 95thP'!$A2,INDIRECT(TEXT(B1,"yyyymm")&"!$A3:A8326"),0),1)

Regards,
Suhas

Thanks Suhas, it's working fine now

combining 'indirect' function with 'index/match"

LinkBack

Thread Tools

Rate This Thread

Display

combining 'indirect' function with 'index/match"

Re: combining 'indirect' function with 'index/match"

Re: combining 'indirect' function with 'index/match"

Re: combining 'indirect' function with 'index/match"

Re: combining 'indirect' function with 'index/match"

Re: combining 'indirect' function with 'index/match"

Re: combining 'indirect' function with 'index/match"

Thread Information

Users Browsing this Thread

Similar Threads

Combine INDEX with INDIRECT + MATCH FUNCTION

[SOLVED] Nested INDIRECT in INDEX/MATCH function

Indirect with Match/Index function

[SOLVED] Indirect Function with Vlookup vs. Index/Match

INDIRECT function together with INDEX MATCH

Bookmarks

Bookmarks

Posting Permissions