Selective Vlookup

[sungen99]

What I am currently doing in this example is in N3 give me the Symbol value in "K" of the Corn contract with the largest Open Interest. In this case it would be "ZCK17". As you can see i am setting the max to D2:D9. This sheet works just fine until i get a new sheet with more data and heaven forbid the corn is not in the right spot.

Ideally what i want is for the program to search for all "CORN" and give the max value so i can say search for the highest corn value in A1:I28.

Thanks for your help.

[TMS]

OK, this is the lazy way with full column references:

Please Login or Register  to view this content.

committed with Ctrl-Shift-Enter rather than just Enter.

Ideally, you need to use a static range which will be greater than the number of rows of data. Or, better, create Dynamic Named Ranges for columns A, B and C. Or, possibly even better, convert your data into a Structured Table and then use Table references for the columns of data. The last would be my preferred option.

[JohnTopley]

in N3

=INDEX($K$2:$K$28,MATCH(MAX(--($B$2:$B$28=M3)*($D$2:$D$28)),$D$2:$D$28,0))

Enter with Ctrl+Shift+Enter...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[sungen99]

Is there a way to do this without arrays? I will be handing this over to someone and they will not know how to do that.

[TMS]

Please Login or Register  to view this content.

Create Dynamic Named Ranges in Name Manager:

dnrSymbol:

Please Login or Register  to view this content.

dnrName:

Please Login or Register  to view this content.

dnrOI:

Please Login or Register  to view this content.

[TMS]

If I knew a way to do it without arrays, I would have suggested it.

Maybe someone else can offer an alternative.

[JohnTopley]

Try

=VLOOKUP(MAX(OFFSET($B$1,MATCH($M3,B:B,0),2,COUNTIF(B:B,M3))),$D$2:$K$1000,8,0)

Assumes column B is sorted as per your file.


OR

=INDEX($A$2:$A$1000,MATCH(MAX(OFFSET($B$1,MATCH($M3,$B:$B,0),2,COUNTIF($B:$B,$M3))),$D$2:$D$1000,0))


From Column A

[sungen99]

JOHN !!!! HOLY S...... this is great. i have NO idea of why it works... HA.... but thank you!!!!!

[sungen99]

John- Actually i found a problem. I have attached my real sheet and it works great for many of the items but for some reason its getting it wrong on a few. See in Col P where it says bad. For some reason its not finding Hard Red Wheat.

Also see how High Grade Copper is showing the correct value for Crude... but then Crude is showing Crude correctly but its not the right month.....

[bebo021999]

Try this non-array formula:

Please Login or Register  to view this content.

Drag down.

[kvsrinivasamurthy]

Just change formula as,

Please Login or Register  to view this content.

[JohnTopley]

With VLOOKUP change: you added an extra row in the file (?)

[sungen99]

Thanks everyone. this is just amazing....