Any MVP's out there ? No one seems to be able to get this one. Conditional Vlookup.

G

guilbj2

Having a ton of trouble getting this one solved ! I'm tryng to re-write
the request in a more sequential order to illustrate the logic I've
worked out.
Here's what I'm trying to do..... (Spreadsheet is 85 columns x 3000
rows)

- Find "Name" in column A (There will be 1-5 duplicates of "Name")

- Check if 505 is found in column CQ of the SAME ROW that "Name" was
found in

- If 505 is present, then the value returned by the formula should be
the value found in CU of the SAME ROW as "Name"

- If 505 does not appear beside ANY of the duplicates of "Name", the
cell should remain blank
 
G

Guest

See my reply to your earlier post, although you have given more information
here than you did then!

Andy.
 
G

guilbj2

Hi Andy,

Thanks for your reply... but I'm a little lost. I don't need to
multiply any of the values. Sumproduct? Maybe I'm not being clear
enough regarding the job codes. They're a numerical value, but aren't
being used to calculate anything.

I'm trying to vlookup a value "Justin" in column A, then check the job
code associated with it in column CQ. If the job code is 505, then the
formula should output the value in CU. Here's an example.
A CQ CU
1 Justin 505 575
2 Justin 506 747
3 Sid 506 800

These values are on tab "Data" with my formula appearing on a summary
sheet "Summary". The formula should return 575 if "Justin" is used,
and return a blank cell if "Sid" is used. Likewise, if I change 505 in
the formula to 506, I should get 747 if I use "Justin" and 800 if I use
"Sid"

Hopefully this makes more sense... I'm starting to lose hope of finding
a way to do this.
 
G

guilbj2

Rod, that's PRECISELY what I'm trying to do, the trick being, that I
need have that formula on a seperate tab of the worksheet. Is it
possible?

The problem is that I need to have the formulas set up on one tab to
provide the final values in an easy to read format for some
non-technical people. The raw data will be dumped into a data tab
daily.

On the summary sheet, I'm just going to have a list of the names of
each agent in column A and the formula (with the name data changed for
each individual time) in the next column.

I don't know if it's relevant, but I'm only looking for about 50 people
in the list of 3000.

I have NEVER been able to stump the brains on this forum... I hope this
isn't the one !
 
G

guilbj2

Hi Andy,

I did try your formula in a little test sheet with no luck. I'm jus
getting a #N/A error. I made a couple slight tweaks to fit it to th
test environment... I've attached a screen shot

+-------------------------------------------------------------------
|Filename: test1.jpg
|Download: http://www.excelforum.com/attachment.php?postid=5161
+-------------------------------------------------------------------
 
G

guilbj2

Hi Andy,

I'm feeling very amateurish, but I managed to figure out why I couldn'
get your formula to work. That of course leads to one more question.
The number of rows in the column won't always be exactly the same. I
there any way to modify the formula so that it will work for any rang
of rows?[

Thanks a ton for the article link btw... making a LOT more sense now
 
G

Guest

Hi

Send me your sheet to bayley_andy at hotmail.com and I'll have a look at it.

Andy.
 
G

Guest

Well, sumproduct won't work with full column ranges, but you could use
A2:A65000 if you like!! Make sure that the range is the same in each bit,
though.

Andy.
 
Top