Lookup that works with out sorting

kluitna

Board Regular
Joined
Mar 10, 2002
Messages
75
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Yep - index / match combinations could be the way to go. Post back with more details about your problem...


Paddy
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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...)
 

kluitna

Board Regular
Joined
Mar 10, 2002
Messages
75
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.
Thank you in advance.
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
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)
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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...

Paddy
 

Forum statistics

Threads
1,143,709
Messages
5,720,391
Members
422,283
Latest member
Parvathy

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