VLOOKUP .. help please

Tim C

New Member
Joined
Oct 7, 2002
Messages
42
I have been using the VLOOKUP Command to have a cell lookup my parts database. Thing is the database is too large a file so I want to split it up by first letter of part reference. I now have this problem:
=VLOOKUP(C13,PARTS,2,FALSE) .. I need the name PARTS to be a variable dependant on the part number I enter, the VLOOKUP should search the proper database.. things I have tried are:
* =VLOOKUP(C13,CELL("contents",C13),2,FALSE)
the CELL command refers to the cell where the part number is located. In the Formula Auditing, I traced it and it looks like it will work but .. doesn't
* =VLOOKUP(C13,W13,2,FALSE)
where W13 is the cell containing the name of database I want VLOOKUP to search.. that didn't work either.. Does anyone have any other suggestions? I have started looking in making a function in visual basic but am not very familiar with this stuff... Is there just something that I'm not seeing?? Thanks for any help that anyone may provide.

Tim C.
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

dvc214

New Member
Joined
Oct 7, 2002
Messages
5
Have you tried using a chain of If formulae?

IF(LEFT(A2,1)="A",VLOOKUP(A2,tableofAs,2,false),IF(LEFT(A2,1)="B",VLOOKUP(A2,tableofBs,2,false),IF ... etc.)

Then you have 26 part tables each beginning with a different letter of the alphabet.

Dave
 

Tim C

New Member
Joined
Oct 7, 2002
Messages
42

ADVERTISEMENT

Ok now I have a new problem... With
=VLOOKUP(B3,INDIRECT(M11),4,FALSE) for example, I now need the database open for VLOOKUP to find the part number I need.. where as if I just typed in the actual name of the database in the VLOOKUP, the database file didn't actually need to be open for this to work.. God it's one thing after another!!! HELP please!
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Yes, the workbook must be open to use INDIRECT as an external reference. I thought your databases were in the same workbook, otherwise I would not have suggested it.

Sorry, I don't know a solution (without VBA), but someone else might.
 

Tim C

New Member
Joined
Oct 7, 2002
Messages
42
I know very little about VBA but I'm willing to try if you know a solution.. what commands to use, I can fill in the blanks.. Yeah the idea is that I am putting together a quote for clientel and by typing a part number, I get the description and cost.. My parts database is huge and I wanted to split it up like: APARTS, BPARTS..
but I don't want these files open.. Thanks for your help.. I am so close.. like I said, if you have any suggestions in VBA, I am willing to try..
 

Tim C

New Member
Joined
Oct 7, 2002
Messages
42
Dave, your solution works but I also have number 0-9 to deal with, as I was typing in the equation I got a message saying formula too long...
 

Forum statistics

Threads
1,144,766
Messages
5,726,167
Members
422,659
Latest member
RGP268

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