VLOOKUP not stopping on first instance of lookup value

[DashboardNovice]

In the attached file, I have a VLOOKUP in cell H7. The look up value of 7.5 appears twice in column C. It appears in C59 and C329. If a lookup value appears more than once, a VLOOKUP is supposed to stop on the first occurrence of the lookup value meaning it should stop on C59, then return 45 degrees,

Why isn't this happening? I have '1' at the 4th and final argument of the VLOOKUP function. I have to use '1' as the final argument because I won't have exact matches all the time. For example, if 11.84 is entered, this doesn't appear as a lookup value, so I have to use '1' to return the nearest value.

 

[ETAF]

you have it sorted descending and also the condition 1 - which means TRUE which means find the nearest value
because its sorted descending it finds the last one
try
=VLOOKUP(H6,$C$14:$D$374,2,FALSE)
and it will get the exact value first in the list

did you want a nearest value - sort the table ascending ?

or use
=INDEX($D$14:$D$374, MATCH(H6,$C$14:$C$374,0))

again exact value

 

[AliGW]

Or:

=VLOOKUP(H6,$C$14:$D$374,2,0)

 

[DashboardNovice]

Thank you everybody for your answers. As I looked through this, I realized that I overlooked something very important. Each value appears twice, Once, in the 1st 180 degrees (which I'm defining as downtrend) and again in the 2nd 180 degrees (which I'm defining as uptrend). I also have to take into account that the user might not enter an exact value, therefore the result would need to be able to return the nearest value.

For example, -12.83333 appears once at 167 degrees and again at 193 degrees, but the user might enter -12.8, -12.82 or -12.84 so I have to take that into account.

Suppose the user enters -12.84 in H7 as the Value and in H8 they want the degrees associated with the 2nd occurrence of -12.84 which means the answer should be between 180-359 degrees. To indicate they want the 2nd occurrence, in H6, for Direction they select Uptrend.

There is no value of -12.84, so the closest value is -12.83333333 and the 2nd occurrence of that corresponds to 193 degrees which is the correct answer, but instead of 193 degrees, H8 returns 167 degrees which is the first occurrence.

So I need to take into account the ability to lookup the 1st or 2nd occurrence of a value entered in H7, and the possibility that the user will enter a value that doesn't match exactly any of the possible lookup values, so return the nearest value.

I know you can concatenate each possible lookup value with COUNTIF to specify the Nth occurrence of each lookup value, but I don't think that will work since the possible look up values go out to 4 digits and also because the user most likely will not enter a value that matches exactly to any of the possible lookup values.

 

[AliGW]

Which version of Excel are you using?

 

[AliGW]

I think you have Excel 2021.

If so, try this:

=LET(downtrend,FILTER(C15:D374,D15:D374<=179),uptrend,FILTER(C15:D374,D15:D374>179),
direction,IF(H6="Uptrend",uptrend,downtrend),type,IF(H6="Uptrend",1,-1),
XLOOKUP(H7,INDEX(direction,,1),INDEX(direction,,2),"",1,type))

 

[AliGW]

Was this of any help to you?

 

[DashboardNovice]

Which version of Excel are you using?

This is the version I have.

 

[DashboardNovice]

I think you have Excel 2021.

If so, try this:

=LET(downtrend,FILTER(C15:D374,D15:D374<=179),uptrend,FILTER(C15:D374,D15:D374>179),
direction,IF(H6="Uptrend",uptrend,downtrend),type,IF(H6="Uptrend",1,-1),
XLOOKUP(H7,INDEX(direction,,1),INDEX(direction,,2),"",1,type))

I think that did it. Thank you very much.

 

[AliGW]

Great.