Can we use Priority while using Single/Multiple Lookup with Multiple Criterions.

I came up with this code but still not gettting the desired result:

INDEX('FarmVille Crops'!$C$2:$L$33,SUMPRODUCT(--('FarmVille Crops'!$F$2:$F$34=$C2)*('FarmVille Crops'!$J$2:$J$34=MAX(('FarmVille Crops'!$J$2:$J$34)*('FarmVille Crops'!$F$2:$F$34=$C2))*('FarmVille Crops'!$G$2:$G$34=MAX(('FarmVille Crops'!$G$2:$G$34)*('FarmVille Crops'!$F$2:$F$34=$C2)))),ROW('FarmVille Crops'!$J$2:$J$34))-1,1)


The problems of giving priority to XP over SanD Dollars..

If i have two Crops with the same profit but one has more XP than the other than naturally the one with the XP would get selected however if the XP is more and theres minor difference in the Sand Dollars then too I want the XP to be given more preference.

In short can we change the priority dynamically in Multiple Lookup Logic?
Please advise...
I have developed this code after a long time as I have always admired the way the gurus have helped me ..

I am really thankful to DaddyLOnglegs,DonketOte,Vane,Shg etc and many more from whom I have got to learn so much..

Dear RatCat,

Yes it does but can u also explain how it works?
Just by Concatenating 11&" "&@ Hours.. Amazing Stuff..

As I also thought of one more thing after I treid my own code as you must have seen the same.

I wanted to give more preference to XP Earned over SandDollars and also maybe keep it floating of possible to intechange between the two
i.e. XP or Sand Dollars so the code should act differently in case of a TIE or if the XP Earned is less and the Profit is More .

I shall try can dome back with the same.

Thanks a lot..

Dear RaTCat,

I will take a while before I get the complete logic absorbed..
I shall come back after that but I must say you really have taken a lot of efforts ( really appreciate that !)

DID u also get the new requirement of keeping the preference of XP over Sand Dollars Dynamic?

I am trying real hard but not able to come up with a good logic yet!

Thanks a lot...

Wow,

It worked very well RatCat, must say that this approach is much more simpler than sumproduct howeer it adds to the number of Sheets which is cause of concern as this is more to do with a game.

And I would also like to know whether the Preferences cans be changed dynamically?

Wow this is great,,

More compact and reobust but what I was talking about was a little complex..

See now in the same example if i want to give the preference to XP so Incase if there's a crop which has more XP abut less Sand Dollars than the second best should be selected as depending on the situation you want to move to the next level in the game whereas if you neeed to buy something you would need more Sand Dollars.

I think your codes excellent and it works brilliantly so no more requirement..Thanks alot again God Bless..!