# Lookup that works with out sorting

#### kluitna

##### Board Regular
I have a question about the lookup function. I use Vlookup quite regularly, but I am in a situation now where items are being added to a lookup list and can't be resorted after the intial list is set up. This gives Vlookup a fit. Is there a way to use a lookup Function in Excel that does not require the list to be sorted.

Any input is much appreciated.
Paul

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Yep - index / match combinations could be the way to go. Post back with more details about your problem...

If you use VLOOKUP with FALSE as the 4th parameter, it'll work perfectly too (If you're looking for exact matches, but the same applies for INDEX/MATCH...)

Thanks,

My problem is I am build workbook that uses a list that is used in multiple worksheets.

A little more background. The workbook is for tracking pay items of a road construction project. Where I am havingteh problem is with the new items and stockpiled items. Which sporadically added over teh couse of a project. So if the list was resorted the previous worksheets would be thrown out of wack when thier values are pulled for a summary sheet that pulls from all the sheets in the order they were created based on the intial list.

but here is the basis of the list
in column A is the Item Number - everything is keyed to this number
colomn B is the item desc
Colomn C is the quantity
D is the Unit Price
E is the total price

All these are pulled into other worksheets by entering the item number.

How would indexing work with the situation?

All you help is greaty aprecieated.

If the Item Number is unique, then you can continue to use VLOOKUP, just make sure that you set the 4th parameter to FALSE (or 0)

Doesn't sound to me as if your problem is as serious as you might think. Does this describe the problem: "Write a lookup formula that will return a value from a list. The list (a) is not sorted, and (b) will grow over time".

If this is right, the solution to each bit is:

(a) use false as the 4th argument (as per Juan's suggestion)
(b) use a dynamic named range as the second argument. By setting up a dynamic named range for your table array, you can ensure that the formulas will always refer to all you data without having to update the table reference each time you add new records.

Search this site for dynamic named range, look especially for posts by Aladin Akyurek. Post back if you need more...

Replies
5
Views
289
Replies
2
Views
227
Replies
3
Views
157
Replies
1
Views
1K
Replies
3
Views
251

1,219,792
Messages
6,150,292
Members
450,949
Latest member
faizanmalik10

### 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.

### Which adblocker are you using?

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

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