Vlookup with nested IF

guilbj2 · #1 08-04-2006, 11:40 AM

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

#2 08-04-2006, 12:00 PM

=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" <guilbj2.2c0vh4_1154706306.9665@excelforum-nospam.com> wrote in
message news:guilbj2.2c0vh4_1154706306.9665@excelforum-nospam.com...
>
> I'm working with some raw data exported to Excel from some tracking
> software we use. For each of our agents (call center), there are
> between 1-5 entries. In column A, their name will appear anywhere from
> 1-5 times (always consecutively) to show stats for each of the queues
> they may have worked on from that day. Column CQ has the number code
> associated with the queue. What I'm trying to do is use Vlookup to
> locate their name, but only return data if CQ of the same row meets a
> value (509) that I specify. If the value in CQ of that row does NOT
> meet the criteria I specify, I need it to then check the next row and
> the next row until it finds the row that does meet the criteria. In
> the advent that none of them do, I plan on simply leaving the cell
> blank. I'm comfortable with teh basics Vlookup and =IF, but I'm
> afraid that making the two work toghether to get this done is beyond
> me. Any help any of you could offer would be most appreciated.
>
>
> --
> guilbj2
> ------------------------------------------------------------------------
> guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
> View this thread: http://www.excelforum.com/showthread...hreadid=568374
>

guilbj2 · #3 08-04-2006, 12:42 PM

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 ?

#4 08-04-2006, 01:00 PM

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" <guilbj2.2c0y95_1154709908.5078@excelforum-nospam.com> wrote in
message news:guilbj2.2c0y95_1154709908.5078@excelforum-nospam.com...
>
> 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 ?
>
>
> --
> guilbj2
> ------------------------------------------------------------------------
> guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
> View this thread: http://www.excelforum.com/showthread...hreadid=568374
>

guilbj2 · #5 08-04-2006, 01:10 PM

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.

#6 08-04-2006, 02:30 PM

=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)

"guilbj2" <guilbj2.2c0zn4_1154711707.4194@excelforum-nospam.com> wrote in
message news:guilbj2.2c0zn4_1154711707.4194@excelforum-nospam.com...
>
> 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.
>
>
> --
> guilbj2
> ------------------------------------------------------------------------
> guilbj2's Profile:
http://www.excelforum.com/member.php...fo&userid=6043
> View this thread: http://www.excelforum.com/showthread...hreadid=568374
>

Welcome to the Excel Forum

Forum Statistics