Combining indirect and index/match

[odonovanc]

Can someone help me combine an indirect with an index/match. Here is my index/match formula:

=INDEX('2017'!H5:S16,MATCH(B9,'2017'!B5:B18,0),MATCH(C4,'2017'!H4:S4,0))

This works perfectly. Obviously it is referencing data in a 2017 tab. But if the date in c4 is a 2018 date, I need it to lookup the 2018 tab instead of the 2017 tab. Hope that makes sense.

 

[arthurbr]

Try =INDEX('2017'!H5:S16,MATCH(B9,IF(YEAR(C4)=2017,INDEX('2017'!B5:B18),INDEX('2018'!B5:B18)),0),MATCH(C4,IF(YEAR(C4)=2017,INDEX('2017'!H4:S4),INDEX('2018'!H4:S4)),0))
(I hope I got the parenthesis right..

 

[odonovanc]

Tells me I have entered too few arguments for this function. WHen I click ok it highlights the b18 in the formula. More info as I don't think the path you started will work. I am starting with a data set I cannot manipulate. So what is on the 2017 and 2018 tabs I have to leave alone. Those tabs have the month in mmm format.

So my c4 sell is also in mmm format. So it might just say FEB. I have in c5 the corresponding year. (2017 in this case). So if we are doing an index and match and not an indirect, I need it to use the 2017 tab if cell c5 is is 2017 and the 2018 tab if cell c5 is 2018.

Hope that makes sense.

 

[Aladin Akyurek]

Try...

=VLOOKUP(B9,INDIRECT("'"&YEAR(C4)&"'!B5:H18"),MATCH(C4,INDIRECT("'"&YEAR(C4)&"'!B4:S4"),0),0)

 

[odonovanc]

I changed your formula to the following:

=VLOOKUP(B9,INDIRECT("'"&YEAR(C5)&"'!B5:H18"),MATCH(C4,INDIRECT("'"&YEAR(C5)&"'!B4:S4"),0),0)

I get a reference error. C5 is where I have the year which is also the tab names (2017 or 2018). c4 is the 3 digit month mmm.

Any ideas?

 

[Aladin Akyurek]

That's what one often runs into when one changes the start situation for which a formula is provided.

Does this meet the changed situation?

=VLOOKUP(B9,INDIRECT(C5&"!B5:H18"),MATCH(C4,INDIRECT(C5&"!B4:S4"),0),0)​

 

[odonovanc]

It is still giving me a reference error

 

[Aladin Akyurek]

It is still giving me a reference error

Try to post B4:H18 from the sheet 2017... (but not an image or a picture).

 

[odonovanc]

I got it working. The following formula for anyone who may stumble on this thread:

=VLOOKUP($B9,INDIRECT(C$5&"!$B$5:$S$18"),MATCH(C$4,INDIRECT(C$5&"!$B$4:$S$4"),0),0)

Changing the first indirect to my whole set of data (going to S instead of H fixed the problem. I added the absolute and relative references for my copying purposes.

Thanks. You are awesome.

 

[Aladin Akyurek]

I got it working. The following formula for anyone who may stumble on this thread:

=VLOOKUP($B9,INDIRECT(C$5&"!$B$5:$S$18"),MATCH(C$4,INDIRECT(C$5&"!$B$4:$S$4"),0),0)

Changing the first indirect to my whole set of data (going to S instead of H fixed the problem. I added the absolute and relative references for my copying purposes.

Thanks. You are awesome.

You are welcome. I seem I picked up the H from your initial attempt and did not notice H and S difference.