Lookup returns incorrect answer

[rodswartz]

I have the following formula LOOKUP($A12,$A$3:$A$8,$D$3:$D$8) which is a portion of another formula to find the corresponding value from the prior week results by team to the current week results. The goal is to create a formula to calculate whether the current result is => than the prior result in columns D, E, and F; failing to do so adds a deduction to the "Deductions" and correspond to the "Less" column above.

Since the rankings (Weighted Score, column C) are sorted by highest to lowest and the order changes I figured the Lookup formula would do the job but keeps giving me 422 regardless of the lookup_value I point to in the A row.

[TABLE="width: 780"]
<colgroup><col span="3"><col><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Team[/TD]
[TD]Period Complete[/TD]
[TD]Weighted Score[/TD]
[TD]Manufacturer Sales (millions$)[/TD]
[TD]Net Income (millions$)[/TD]
[TD]Stock Price ($)[/TD]
[TD]Capacity Utilization[/TD]
[TD]Shelf Space[/TD]
[TD]Initial Score[/TD]
[TD]Less[/TD]
[TD]Team Score[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]97.5[/TD]
[TD]508[/TD]
[TD]138[/TD]
[TD]65.12[/TD]
[TD]98.40[/TD]
[TD]1.79[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]85.8[/TD]
[TD]462[/TD]
[TD]107[/TD]
[TD]54.09[/TD]
[TD]100.10[/TD]
[TD]1.65[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]60.6[/TD]
[TD]422[/TD]
[TD]59[/TD]
[TD]39.45[/TD]
[TD]80.20[/TD]
[TD]1.94[/TD]
[TD]18[/TD]
[TD]0[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3[/TD]
[TD]58.3[/TD]
[TD]425[/TD]
[TD]57[/TD]
[TD]38.23[/TD]
[TD]105.70[/TD]
[TD]1.58[/TD]
[TD]17[/TD]
[TD]1[/TD]
[TD]16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]57[/TD]
[TD]407[/TD]
[TD]61[/TD]
[TD]37.07[/TD]
[TD]101.10[/TD]
[TD]1.43[/TD]
[TD]16[/TD]
[TD]1[/TD]
[TD]15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Prior:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Team[/TD]
[TD]Period Complete[/TD]
[TD]Weighted Score[/TD]
[TD]Manufacturer Sales (millions$)[/TD]
[TD]Net Income (millions$)[/TD]
[TD]Stock Price ($)[/TD]
[TD]Capacity Utilization[/TD]
[TD]Shelf Space[/TD]
[TD]Deductions[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]94.6[/TD]
[TD]454[/TD]
[TD]96[/TD]
[TD]54.58[/TD]
[TD]105.00[/TD]
[TD]1.65[/TD]
[TD]0[/TD]
[TD="align: right"]422[/TD]
[TD="colspan: 2"]<-Should be 508[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]92.6[/TD]
[TD]434[/TD]
[TD]107[/TD]
[TD]52.43[/TD]
[TD]95.60[/TD]
[TD]1.48[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2[/TD]
[TD]75.3[/TD]
[TD]425[/TD]
[TD]70[/TD]
[TD]44.10[/TD]
[TD]104.90[/TD]
[TD]1.48[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]61.9[/TD]
[TD]408[/TD]
[TD]52[/TD]
[TD]37.23[/TD]
[TD]105.70[/TD]
[TD]1.45[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]52.5[/TD]
[TD]379[/TD]
[TD]34[/TD]
[TD]33.70[/TD]
[TD]120.00[/TD]
[TD]1.15[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

Any help would be appreciated!

 

[Jonmo1]

Welcome to the board.

Lookup requires the data in the lookup table ($A$3:$A$8) to be sorted in Ascending order.

Try VLOOKUP instead
=VLOOKUP($A12,$A$3:$D$8,4,0)

 

[AliGW]

Try this:

=INDEX($D$3:$D$8,Match($A12,$A$3:$A$8,0),0)

 

[rodswartz]

That works great! And I get the 4 is for the 4th column of the array so I'd use 5 and 6 for the comparisons of the next two numbers. What would be the best way to use the VLookup portion to compare whether to add 1 to a counter if columns 4, 5, and 6 are less than the prior period? An IF statement would seem a bit much... Thanks so much!!

 

[rodswartz]

Thanks for the idea. I'm sure how to use your formula setup, though it produces the correct answer, to compare the other three columns and then compare whether them to see if a point is deducted for having the prior period being less than the current one.

 

[Jonmo1]

Index Match as shown by AliGW would probably be better sutied to pulling 3 columns worth of results.
Just adjust the absolute reference to the D column in the index.

=INDEX(D$3:D$8,Match($A12,$A$3:$A$8,0),0)

As you drag it to the right, the D incriments automatically to E and F.


Then this would give a True or False if it's Less than D12
=INDEX(D$3:D$8,Match($A12,$A$3:$A$8,0),0) < D12<d12<d12< html=""></d12<d12<>

 

[rodswartz]

Thanks...honestly if I don't understand how it's working (it's me!) I can't replicate it. So If used your prior one and tried the If + If + If and changed the column count to get it to work!

=IF(VLOOKUP($A3,$A$3:$F$7,4,0)<VLOOKUP($A3,$A$13:$F$17,4,0),1,0)+IF(VLOOKUP($A3,$A$3:$F$7,5,0)<VLOOKUP($A3,$A$13:$F$17,5,0),1,0)+IF(VLOOKUP($A3,$A$3:$F$7,6,0)<VLOOKUP($A3,$A$13:$F$17,6,0),1,0)

It might be cumbersome but it works!! Thanks again as I've been trying to figure this out for about 10 months and now I can grade my students' results much more confidently. Take care.

 

[Jonmo1]

You're welcome.