In the attached spreadsheet I'm using VLOOKUP to create a cross reference between worksheets JS and ITEM. If you will look at the ITEM worksheet cell reference H13 & H14. The correct value for H13 should be AMC, not 729. Is there a way to use the value in the Class column and Item ID column in combination to get the value AMC? Would MATCH & INDEX work? I'm not familiar with Match & Index. I'd appreciate some help here. I've got 15,404 records to evaluate this way. Thanks
Last edited by nander; 08-30-2007 at 08:31 AM.
I'm confused by your request.
Looking at the JS table, the VLOOKUP for Part Number 7000 SHOULD return 729 (that is in Column 3 of your table). From where should excel get AMC?
I've updated the attached file. You will notice in cells H13-H15 the result is AMC. AMC is correct for cell H13 but not for H14 or H15. See what I have in this small example is 3 separate Vendors but these 3 vendors have the same Vendor Part number. In a section below the main group of examples,(same worksheet) I have the 3 items with their correct vendors.
The problem is the lookup is match the first 7000 value on the JS worksheet, which is column 15. your products need to have unique manufacture part numbers for this to work, at the moment you have 3 items with 7000 as their manuf part number.
just finish my dinner hour so i will have to have another look either tonight or tomorrow.
It seems like the information your pulling is one of the categories you want to match up... (i.e. Class in one sheet is the same thing as Vendor Code in the other)Originally Posted by nander
If so try this in H2:
which must be confirmed with CTRL+SHIFT+ENTER not just ENTER... you'll see {} brackets appear around the formula....then copy down the formula.
Please Login or Register to view this content.
If you make any revisions you will have to reconfirm with the CSE key combo.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi
try this hope it helps
Fletch
Fletch the file was corrupted. Could you resend?
Did you try my suggestion? Did it not work?
Yes I started to do that after I got the corrupted file. I'll let you know
Yes, I tried it and it works except I get a #NA for one of the values. I've attached the updated file. Can you explain to me what I need to do to correct that, thank you.
The Class in Column D for that particular item was formatted as text, whereas it's matching Vendor Code on the JS sheet is formatted as general...
I converted the Class to number to match the JS sheet...
Explain to me how to convert the CLASS to number? In certain cells I notice a small green angle in the top left corner of the cell. I'm guessing it relates to general or text or number.
Yes Sir!
When you click on that cell, you'll see a little icon popup (an ! mark in a diamond shape) with a drop down option. Click on that drop down and select Convert to Number. The first item in the list tells you the issue (i.e Number stored as text).
I have the orginal file however its 3,338 KB and I can't upload it here. I'd like, if you don't mind, and inclined allow me to send you the file via yousendit. I'm trying to make your suggestions work but I'm struggling with the formula. Would be very greatful. Thanks
Please see your PMs
Hi
here it is again.....hope it works
regards
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks