Vlookup with nested IF

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 ?

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
>

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.

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