VLookup returning same data when multiples exist

**Laserfast** · 12-13-2010, 05:19 AM

Hi Gurus, I have spent a weekend trying to get the table attached to work using False, True, $ and the usual suspects. Any feedback to point me in the right direction would be id didly good.

It seems to half work. Basically I am trying to do a Vlookup from one sheet to another. The aim is to send toner prices for customer requests. A customer usually tells me the model number of the printer they have. This would be used to pull in data using Vlookup to complete the description and price. The problem is one model number may have several or more toners so I need to include all these in the table. It seems to work at the beginning of the template returning the correct data when I have say model number 5 with 3 different toners but later it just keeps bringup the same data and not the corresponding data it should.

Any ideas whats going wrong? Please help before I jump off a bridge!

**preond** · 12-13-2010, 05:54 AM

Hi,

Try using the vlookupnth function. follow the link below.

http://www.vbaexpress.com/kb/getarticle.php?kb_id=8

**Marcol** · 12-13-2010, 07:37 AM

Try this

Column C now contains a drop-down from a dynamic named list for unique Model Numbers.
When a selection is made in Column C, a new dropdown is created in Column D with the Part Numbers relevant to that Model. When a selection is made from there the remaining data is filled.

You are making life hard for yourself by using merged cells.
I can see no reason for merging the rows in columns E:I, J:K, & LM
If I were you I would reconsider the sheet layout using column widths to get the best and easiest solution.

Avoid merged cells for all cells that contain tabulated data, better still, avoid them altogether!!!

Hope this helps

**mahju** · 12-13-2010, 08:28 AM

Try use $ sign in table array argument of Vlookup function. I have changed the formula in

above few rows check and copy down.

See attached sheet

**DanExel** · 12-13-2010, 09:04 AM

I,m very impressd with Marcol's answer to the problem.
My alternative do not involve macros.
Ad a suffix to the model numbers ie. -1,-2,-3 etc. and Bob's your uncle.
See attachment
Regards
Dan

**Marcol** · 12-13-2010, 09:29 AM

@ mahju

This is what is required, your amendment, although correct in principle, i.e. using $ anchors, doesn't return this.

Please Login or Register  to view this content.

**mahju** · 05-11-2011, 01:18 PM

I have an idea about this thread so i think this should be shared.
It may be used by other users also if not used by thread starter

Thanks

Regards

**Marcol** · 05-11-2011, 03:03 PM

I also have an idea about this thread.

Its DEAD as a doornail.

Laserfast has not replied once since posting in December last year!

**mahju** · 05-12-2011, 12:56 PM

Originally Posted by Marcol

I also have an idea about this thread.

Its DEAD as a doornail.

Laserfast has not replied once since posting in December last year!

Respected sir,
I am sorry for the time you have spent on reading and replying my msg.
I just answer because i think thread is interesting and may be used by new user and guest.

I shall try not to reappraise any dead question (Not followed for more than 7 days) in future

Thanks
and
Best regards