lookup function limitation?

[immyjimmy]

Can I use a vlookup or hlookup formula to scan a range that is not in order? Or do I need to use an index/match combination? I have a range that is input by a user (numeric part numbers), but not in any order. Currently, my vlookup function "=VLOOKUP($B116,$B$7:$G$104,2)" works only if column B is in numerical order. If I am looking for a value of 12335, as soon as I have a sequence like:

12345
12335

the function returns the dreaded #N/A. If the list is in numerical order, it wroks just fine... HELP?

Jim

 

[DonkeyOte]

you just need to amend your lookup

=vlookup(test,range,col,FALSE) this checks for EXACT match only - therefore order is irrelevant.

Luke

 

[immyjimmy]

lasw10,
That did it. Thanks so much!

Jim

 

[PocketNeo]

I find that the use of a NAME reference is best. A lot of users I help out seem to use ranges, and it causes lots of problems when they don't use the $ and do a fill-down.

BTW, I use FALSE all the time.

 

[Aladin Akyurek]

On 2002-11-05 15:30, PocketNeo wrote:
I find that the use of a NAME reference is best. A lot of users I help out seem to use ranges, and it causes lots of problems when they don't use the $ and do a fill-down.

BTW, I use FALSE all the time.

I don't think that's a good idea: "FALSE all the time" makes the following computations with VLOOKUP impossible...

Book4
	A	B	C	D	E	F	G
1	SalesPerson	Sales	Commission		CommissionTable
2	bob	1000	50		0	0.00%
3	bill	1300	65		500	4.00%
4	jim	1250	62.5		1000	5.00%
5	neo	700	28		2000	7.00%
6
Sheet1

 

[DonkeyOte]

Pocket Neo - re: named ranges, I USED to agree (I never used range fields), however, this is not always the case - NAMED fields are not always the best as they are not as flexible as a range - a range can be a variable all the time without the need of setting up multiple named ranges. Using variables makes life a lot easier. I agree that if the vlookup & the data source is never likely to change then named fields make a lot of sense, but this is not always the case.

 

[PocketNeo]

I see both your points.
Perhaps with respect to what users at my firm do, a name reference makes a lot of sense. But, I found that a non-named reference caused errors. That is, when I changed the reference to a Name reference, it fixed the problem. It was really strange.
I'll keep an eye out for a similar problem and when it happens again, I'll post it here.