INDIRECT() returns #VALUE error and I don't know why!

[baseballprof]

I'm trying to use the INDIRECT function in combination with the INDEX MATCH functions to look up certain values associated with different baseball players' names (if my profile name didn't give that away!). I've developed the following formula...

=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,("A"&D2&":"&"A"&E2),0))

...where D2 returns 101 (the first row after A100) and E2 returns 1381 (the last row in my data). According to the Evaluate Formula function in Excel, this formula produces a #VALUE error when it reduces down to the following:

=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,"$A$101:$A$1381",0))

I know that the quotation marks surrounding $A$101:$A$1381 would prevent Excel from reading it as a number, so is that my issue? Regardless, any ideas to help fix it?

Note: If I eliminate the INDIRECT altogether and just enter =INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,$A$101:$A$1381,0)) from scratch, it returns the proper value...little bit stumped. Thanks!

 

[T. Valko]

I'm trying to use the INDIRECT function in combination with the INDEX MATCH functions to look up certain values associated with different baseball players' names (if my profile name didn't give that away!). I've developed the following formula...

=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,("A"&D2&":"&"A"&E2),0))

...where D2 returns 101 (the first row after A100) and E2 returns 1381 (the last row in my data). According to the Evaluate Formula function in Excel, this formula produces a #VALUE error when it reduces down to the following:

=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,"$A$101:$A$1381",0))

I know that the quotation marks surrounding $A$101:$A$1381 would prevent Excel from reading it as a number, so is that my issue? Regardless, any ideas to help fix it?

Note: If I eliminate the INDIRECT altogether and just enter =INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,$A$101:$A$1381,0)) from scratch, it returns the proper value...little bit stumped. Thanks!

Yes, that is one issue.

However...

Why are you using INDIRECT in the first place?

Also, you're indexing a range on the Batter Universe sheet but your attempted INDIRECT range is not pointing to the same sheet?

Can you just do this:

=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,'Batter Universe'!A:A,0))

Or...

=VLOOKUP('Player Universe'!A100,'Batter Universe'!A:D,4,0)

 

[Jonmo1]

Welcome to the board..

You didn't actually put INDIRECT in your formula...

=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,("A"&D2&":"&"A"&E2),0))

Try
=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,INDIRECT("A"&D2&":"&"A"&E2),0))

 

[baseballprof]

Thanks for the reply.

Unfortunately I have many duplicate values in A:A (upwards of 4) each with unique values in D:D. My ultimate goal is to return the largest value in D:D for each unique value in A:A. Initially I was able to create an array formula that accomplished this, but with 1,381 total entries in A:A it ran way too slowly, something on the order of 1-2 minutes per recalculation.

Now I'm trying to return each unique value in a different column so I can then just use the MAX function. So, for example, 'Player Universe'!A100 has three occurrences in 'Batter Universe'!A:A. My idea was to use the INDIRECT to refine the search field in 'Batter Universe'!A:A after each occurrence so that 'Player Universe'!B100 would return the first value in D:D, C101 would return the second, and D101 would return the third. Then I would use =MAX(B101:D101) to find the highest.

Thanks for reading!

 

[baseballprof]

Welcome to the board..

You didn't actually put INDIRECT in your formula...



Try
=INDEX('Batter Universe'!D:D,MATCH('Player Universe'!A100,INDIRECT("A"&D2&":"&"A"&E2),0))

Oh. My. God. I feel like an idiot. All of these references were getting to me and I completely missed that. You, sir, are a savior.

And thanks to you too, T. Valko. I appreciate the time!

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[T. Valko]

Oh. My. God. I feel like an idiot. All of these references were getting to me and I completely missed that. You, sir, are a savior.

And thanks to you too, T. Valko. I appreciate the time!

Sounds like you got a solution.

Thanks for the feedback!