Vlookup with nested IF

[guilbj2]

I'm working with some raw data exported to Excel from some trackin
software we use. For each of our agents (call center), there ar
between 1-5 entries. In column A, their name will appear anywhere fro
1-5 times (always consecutively) to show stats for each of the queue
they may have worked on from that day. Column CQ has the number cod
associated with the queue. What I'm trying to do is use Vlookup t
locate their name, but only return data if CQ of the same row meets
value (509) that I specify. If the value in CQ of that row does NO
meet the criteria I specify, I need it to then check the next row an
the next row until it finds the row that does meet the criteria. I
the advent that none of them do, I plan on simply leaving the cel
blank. I'm comfortable with teh basics Vlookup and =IF, but I'
afraid that making the two work toghether to get this done is beyon
me. Any help any of you could offer would be most appreciated

 

[Bob Phillips]

=IF(ISNA(MATCH(1,(A1:A1000="name")*(CQ1:CQ1000=509),0)),"",INDEX(A1:A100,MAT
CH(1,(A1:A1000="name")*(CQ1:CQ1000=509),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[guilbj2]

Hi Bob,

Thanks a ton for your input. I omitted one item that I'll need the
formula to accomplish... I guess I figured I'd be able to sort it out
once I got the lookup issue resolved, but your formula is definitely
above my pay grade and I'm a little uncertain of where to insert it.

The value that I need returned is column CU/ column M, with the row
being determined by the row 509 (or other value i choose) is located
next to the person's name. If the 509 value is located next to the
person's name @ CQ?, the value I need returned is =CU?/M?, otherwise,
the cell remains blank.

I tried pasting your formula into my sheet to try and play with it to
see where I could get, but it only returns a #VALUE!

Any chance you have time to indulge me w/ a follow up ?

 

[Bob Phillips]

Which is it, CU or M? What I gave you looks for name in column A, 509 in CQ,
and returns that name if found, else blank.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[guilbj2]

Sorry, I'm doing a poor job of getting my message accross here. Let me
try it from scratch.

Names are found in A1:A3000. Sometimes the same name will repeat
itself consecutively in column A, so the same name value could appear
in A1, A2, A3, A4, A5. In column CQ, their skill set appears, which
basically tells me what stats are in that row. This is the 509 value
(one of about 6 diff options).

I need to search column A for a name I specify, and find which, if any,
of the rows with the name in it also includes 509 in column CQ. If that
does occur, I want to excute the formula =CU?/M? with ? being the same
row as the name and 509. If no row includes the name I've entered with
509 in CQ of the same row, the cell remains blank.

Hopefully that's a little more clear.... thanks immensely for your
patience thus far.

 

[Bob Phillips]

=IF(ISNA(MATCH(1,(A1:A1000="name")*(CQ1:CQ1000=509),0)),"",
INDEX(CU1:CU100,MATCH(1,(A1:A1000="name")*(CQ1:CQ1000=509),0)/
INDEX(M1:M100,MATCH(1,(A1:A1000="name")*(CQ1:CQ1000=509),0))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)