VLOOKUP help

  • Thread starter Thread starter Stuck Again
  • Start date Start date
S

Stuck Again

I have tried my best to figure out how to use this
function, but I can't. I'm not even sure if it would help
me perform the function I need it to perform.

I have a spreadsheet for auditting "lottery tickets". As
the audit can encompass 400 - 2000 tickets at a time I am
looking for ways to spead up the data entry process. Of
the 400+ tickets... there are about 10-20 different
types. Each individual "unit" of tickets has a serial #.

Generally units of the same type, have the same details
(ie Quantity, Price, Net Revenue). Rather than having to
type in this info for each unit, I am looking for a way to
just "pull in" the related information.

Here is a sample of an audit as I would record from the
purchases: (for example purposes lets call this worksheet
#2)

Name Serial# Color Quantity Price Net Revenue
Lucky 9's 122001 Red 2000 .50 $290
Lucky 9's 122003 Red 2000 .50 $290
Red Hot 065023 Green 2250 .50 $600
Red Hot 065025 Green 2250 .50 $600
Lucky 9's 123009 Blue 2000 .50 $290

Ideally what I would like to do, is that when the user
enters in the Name, then the "Quantity, Price and Net
Revenue" are automatically displayed in the corresponding
rows.

I'm assuming I would have to define the data relation in a
seperate worksheet -- however as it would need to be set
up so that an inexperienced person with Excel would even
understand.

Ideally, all the person would do in the data relation
worksheet would just type in the unit info. (for example
purposes lets call this worksheet #1)IE:

Name Quantity Price Net Revenue
Lucky 9's 2000 .50 $290
Red Hot 2250 .50 $600

So in summary, on worksheet #2, in the Name cell, if the
Name that appears matches a name in the Name column on
worksheet #1, then I need to pull in the info for Quantity
Price and Net Revenue from worksheet #1 and display in the
appropriate columns on worksheet #2.

I hope I made this clear. I'm not sure if VLOOKUP is able
to do this, but if it can please let me know. If so, then
its off to the bookstore for Excel for Dummies!

Thanks
 
VLOOKUP will work for you:


Sheet2:

A2: Lucky 9's
B2: =VLOOKUP($A2, Sheet1!A:F, 4, FALSE)
C2: =VLOOKUP($A2, Sheet1!A:F, 5, FALSE)
D2: =VLOOKUP($A2, Sheet1!A:F, 6, FALSE)

Copy down as far as necessary. If you want to preload your formulae
but have the cells display blanks until the Name is entered:

B2: =IF($A2<>"", VLOOKUP($A2, Sheet1!A:F, 4, FALSE), "")
etc.
 
I don't fully understand exactly what you want. Your example is not quite
clear to ME.
if you want to just show all of the items with Lucky 9's then use
data>autofilter
if you want a SUM use sumif or sumproduct
 
Back
Top