Vlookup clarifiication

[OceanPlanet13X]

Hi all

Vlookup formulas: does the lookup range have be in descending order.

Thanks in advance

 

[Dartagnan]

Shouldn't matter. Main requirement is the lookup_value must be in the left column.

 

[Aladin Akyurek]

Hi all

Vlookup formulas: does the lookup range have be in descending order.

Thanks in advance

No...

=VLOOKUP(A2,$F$2:$G$100,2,1)

presupposes that F2:G100 is sorted in ascending order on F2:F100.

=VLOOKUP(A2,$F$2:$G$100,2,0)

does effect an exact match between A2 and the values in F2:F100 and does not presuppose any sorting.

VLOOKUP does not expect a descending sort order at all, but see the MATCH function for that.

 

[T. Valko]

Hi all

Vlookup formulas: does the lookup range have be in descending order.

Thanks in advance

Short answer is no.

But, it depends on the application.

Typically, you only need to have the data sorted when you're looking up numeric values and need a "closest" match if an exact match doesn't exist.

For example, an income tax table. There's a range for the lookup value to fall into so there may not be an exact match:

From 0 to 9,999.99 = Rate1
From 10,000 to 19,999.99 = Rate2
From 20,000 to 39,999.99 = Rate3
From 40,000 to >40,000 = Rate4

In an application like this then the data would need to be sorted in ascending order like this:

Book1
	A	B
2	0	Rate1
3	10000	Rate2
4	20000	Rate3
5	40000	Rate4
Sheet1

 

[OceanPlanet13X]

thanks for all the reply

 

[T. Valko]

thanks for all the reply

You're welcome!

 

[Aladin Akyurek]

thanks for all the reply

You are welcome. Thanks for providing feedback.