Can I use VLookup for Multi items?



Here is my desire, however I do not have Access which I understand would
probably work better.

I want to create an inventory of tool/parts in my home/ workshop.
EX: 3/8 Nut withthe thread (24) count (decription) , I could possibly have a
similar description. I would assign a part number for my look up table, qty,
desription, location.
Also tools (handy if I loan to neighbor to have box who has it!).

Now If I want to do a VLookup on a description "1/2 Nut...." and I have
different size length, which might have multi retruns how would I list the
extra, since lookup will return the first Item.

I use look up all the time and understand the need to sort.
I work at a location that had 600 printers which I needed to know the
location, IP, type of printer. So I know how to create, a box to enter (data)
then result in all the info including the line number located in list in case
I need to enter/ edit for that item.

But searching for text with multi responses I have little knowledge.
Any Hints.
I have not used or created Pivot tables which I have seen people mention.
Thanks for any help.




You may try something which I do...

Suppose you have your lookup table in A:D in Sheet 2 and you want to do a
lookup based on the values in A-C...
Insert Col A (this will shift the value to lookup to col E)
Use the formula
and copy down...

Now do a lookup in Sheet1 use



Shane Devenshire


You could just apply an auto filter to your data. Suppose the data is in
A1:D600 with the description you want in column C and titles on row 1.

Highlight the data and choose Data, Filter, AutoFilter. Click the drop down
arrow on the Description column and pick the one you want. Done.

Comment: VLOOKUP does not require sorting the data when you are doing and
exact match lookup (fourth argument FALSE), it does require ascending sorting
on the lookup column, if you are doing approximate matches.

A pivot table will also work for you problem. And there are complex
formulas to produce the same results.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question