VLOOKUP - to return a value based on what I input to a cell?

**AndyHawke** · 09-11-2012, 09:55 AM

Hi,

I admit I know very little about vlookups. Had training on them a couple years back but haven't used one since.

I have a list of products and sales figures. What I want is to be able to type into a cell the code of the product, and then for the vlookup to give me the sales of that product.

Here's an example:

A B
1 Product Name Sales
2 ASDF - Class 4 widget 10
3 ASDFG - Class 4 widget with bells on 15
4 QWER - Elbow grease 100ml 20
5 QWERT - Elbow grease 250ml 3
6 QWERTY - Elbow grease 500ml 5
7 ZXC - Left handed screwdriver 7
8 ZXCV - Right handed screwdriver 654
9 ZXCVB - Dual hand screwdriver 311

So - column A has products, column B sales.

I want to type the product code (ASDF, QWERT etc) into cell C1 and have cell D1 tell me the sales.

Any idea on the formula please?

Best regards,

Andy

**ChemistB** · 09-11-2012, 10:05 AM

Hard to tell from your post but is the product code in column A all by itself? If so, then, let's say you project code is in G1, the VLOOKUP would look like this

=VLOOKUP(G1, $A$2:$B$9,2,FALSE)
2 means return the second column and FALSE means you are looking for exact matches only.
Does that help?

**FCarv** · 09-11-2012, 10:05 AM

Hello Andy,

Can you tell me if Column A has, having in mind the first example, the data information displayed like ASDF only or ASDF - Class 4 ?

EDIT: I'm always late

Kind Regards,
FCarv

**Kevin UK** · 09-11-2012, 10:15 AM

Something like this!

**AndyHawke** · 09-11-2012, 10:38 AM

Hi - that's great thanks for your help so far!

Column A will have the product name "ASDF - Class 4 widget"
Column B will have the sales "10"

So - if I type in ASDF in the search cell the vlookup won't come back with anything... because multiple cells contain "ASDF". Ideally what I'd like is for the total sales for all products containing ASDF in the name to be added up if I search for "ASDF".

Sorry for the rubbish explaination, and thanks for the responses so far - they have helped - but now I'm thinking this problem can be solved in an even better way.

Thanks,

Andy

**thameem127** · 09-11-2012, 10:46 AM

Upload file to solve

Thanks
Thameem

**FCarv** · 09-11-2012, 10:59 AM

Andy, if you use ChemistB formula with a small modification on it you should get the result you want:

Please Login or Register  to view this content.

EDIT: Sorry - Guess it won't work. I just tried.

**ChemistB** · 09-11-2012, 11:07 AM

FCarv's formula will work if your products are in alphabetical order like your example. If they are not, it won't work.

Another option
=VLOOKUP(G1&" *",$A$2:$B$9,2,FALSE)
make sure you enclude the blank space inside the quotes.

**FCarv** · 09-11-2012, 11:11 AM

Nice, I will keep that for myself as well

**Kevin UK** · 09-11-2012, 12:25 PM

Hi Andy

You can total sales for all products containing "ASDF" using the SUMIF or SUMIFS Function

If your Products are in Column A = A2:A9 and your Sales are in Column B = B2:B9 Criteria in G1 = ASDF

Formula:

Please Login or Register  to view this content.

**AndyHawke** · 09-24-2012, 09:42 AM

Hi All - thanks so much for all of the information in this thread. In fact I used a different method to resolve... as I couldnt access the internet for a while (in this day and age!) to check this thread.

Thanks again for your help!

**ChemistB** · 09-24-2012, 10:27 AM

Curious as to what method you used? Always willing to learn here.

**AndyHawke** · 09-24-2012, 10:31 AM

Originally Posted by ChemistB

Curious as to what method you used? Always willing to learn here.

Oh it was nothing - just a bunch of formulas but took about half an hour as it required a formula for each product. Since I don't want to do that each time I will use the above answers in the future when I again do this piece of work.

**Cutter** · 09-24-2012, 07:57 PM

@ AndyHawke

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**AndyHawke** · 09-25-2012, 04:31 AM

Hi Cutter,

Sorry - completely forgot about both of those. Thanks for marking the thread solved and I've now given reputation points also.

Best regards,

Andy

VLOOKUP - to return a value based on what I input to a cell?

LinkBack

Thread Tools

Rate This Thread

Display

VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Re: VLOOKUP - to return a value based on what I input to a cell?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions