lookup function limitation?

immyjimmy

Active Member
Joined
May 27, 2002
Messages
251
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
you just need to amend your lookup

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

Luke
 

PocketNeo

Board Regular
Joined
Jun 10, 2002
Messages
139
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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
ABCDEFG
1SalesPersonSalesCommissionCommissionTable
2bob10005000.00%
3bill1300655004.00%
4jim125062.510005.00%
5neo7002820007.00%
6
Sheet1
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Board Regular
Joined
Jun 10, 2002
Messages
139
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.
 

Forum statistics

Threads
1,147,455
Messages
5,741,222
Members
423,649
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top