Complex Lookup - pivot problem?

I need help with an advanced Lookup. I’ve done a search for something similar and can’t find anything. Sorry in advance if this is easy for some.

See enclosed example file. The function I’m looking for is for the "Summary – 3 counties" sheet. Line by line, I look for the MAX number (I can do that) and according to where it is, it would then go to that particular corresponding sheet (I can do that), that particular corresponding line (according to species) and same MAX number and return the header in that same column. For example, for "Montezuma Quail", the MAX would return 7.045. The function would then lookup in the "Pima" sheet on the "Montezuma Quail" line for 7.045 and return the header of "Madera Canyon--Proctor Rd.". Any ideas how I can do this in one line? Is this what is called a pivot? Or will this require a bit of VBA of which I know nothing?

Thanks.

Hi gapollo,

No it isn't a Pivot Table issue!

I don't understand what you are trying to achieve, but this formula might give you a clue. In cell E2, it gives you the answer you wanted.

=INDEX(Pima!$B$1:$EQ$1,0,MATCH($C2,INDIRECT("Pima!$B$" & MATCH(A2,Pima!$B$1:$B$147,0) & ":$EQ$" & MATCH(A2,Pima!$B$1:$B$147,0)),0))

I'm sure this is just the first step!

This is already a complex formula - if you take it further, I would recommend using some helper columns.

Is your plan to have the second best (Cochise) on column F and get Huachuca Canyon?

Don't have time at the moment to do more, but let me know how this goes, and will have a look tomorrow.

Regards,

David


When you reply please make it clear WHO you are responding to by mentioning their name.

If this has been of assistance, please advise. A little thanks goes a long way.
- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

David Coop,

Thanks for the ideas. I have been able to engineer something out from this so that everything is automatic.
As it happens, you guessed right about my wanting the second best in the next column and I've been able to work that out as well.
Your help was much appreciated!

AliGW,

As suggested, I had already adapted both the "LARGE" idea as well as the "IFERROR" idea into my second column before seeing the suggestions. Thank you - it will greatly help others.

You're welcome gapollo, and thanks for the Rep.

DAC