Vlookup with IF

[guilbj2]

Hi All,

I have a sheet of raw data (tab named "Data")about 85 columns wide by
3000 rows. Each row will have a person's name in column A. The name
will appear between 1-5 times depending on how many job codes that
particular person worked for that particular day.

On a seperate tab ("Summary"), I have a summary since the data sheet is
a little overwhelming. What I'm trying to do is the following.

Search for a name in column A. Once the name is located, check cell CQ
of the same row to see if it matches the job code i'm looking for. The
job codes are 505,506,509,511,527. I intend to re-use the same formula
and simply change the code I've entered into it for different sections
of the summary. If the code matches, I want the value of the cell to =
CU of the same row. It's important to note that the same name will
appear multiple times though... if the first one doesn't meet the
condition, I need to check the other entries, not simply ignore them.

Anyone able to shed some light?

 

[robert111]

..............................code1.........code2..........code3
tom.............................................1
dick................................................................1
tom................................................................1

=sumproduct((A2:A100="tom")*(B2:B100=1))+sumproduct((A2:A100="tom")*(c2:c100=1))+sumproduct((A2:A100="tom")*(d2:d100=1))

this gives you how many jobs tom has done

if you set up a matrix with names down the first column and codes a
column headers, you can reference these in the sumproduct formula, s
just one formula can be copied to the whole of the table. You then ca
see a summary of who has done which jobs

 

[Guest]

Take a look at the feature Data > Filter > AutoFilter......there you can
select items from the dropdown arrows at the top of each column to select any
set of conditons you wish to view............the do Data > Filter >
AutoFilter again to reset everything back to normal........

hth
Vaya con Dios,
Chuck, CABGx3

 

[guilbj2]

Sorry guys, but this isn't at all what I need. I'm not trying to count
the number of jobs done, nor do I need to filter for one job code.I
know how to use filters etc. but my user base for this sheet can not
and has no intention of learning. Not even close. It's a miracle they
don't rip out their own CD drives when the drink holders get wobbly, get
my drift? I HAVE to do this as a formula on a nice summary sheet, so
all the end users have to do is look at it. I'm dumping the data into
one tab of the sheet each day, and using the summary sheet to provide a
user friendly overview. That means no manipulation by the user
whatsoever. The data sheet will look something like this...

Justin 505 874
Justin 506 753
Sid 505 987
Mark 506 855

I basically want to Vlookup Justin, confirm that 505 is in the job code
field and then have the formula provide the value of CU (753 in this
case). When that name and job code entered into the formula don't
appear on the same row, the cell remains blank.

 

[Guest]

You could Concatenate the Name and JobField into a helper column to the left
of your existing table and do your VLOOKUP on that column.....something like

=VLOOKUP(Name&JobCode,NewHelperColumn,4,false)

hth
Vaya con Dios,
Chuck, CABGx3

P.S. The Autofilter can be run by macro should you ever have the need.

 

[Guest]

Hi

Try this:
=SUMPRODUCT((A2:A5000="Justin")*(B2:B5000=505")*(CU2:CU5000))
presuming your names are in column A and your jobs are in column B

Hope this helps.
Andy.