Nesting If/Then and Vlookup

**JoJoGrl** · 06-30-2013, 09:08 PM

For example:

column b2 has list of types of supplies.
If b2 says "pencils" I want it to lookup the ID number in Column c2 and look up a price on sheet 1 column 2.
If b2 says anything besides pencils i want it to look up the ID number in column c2 in sheet 2 column 49

I've done this before but for some reason i can't it to work and i've tweaked the formula so many times I'm starting to confuse myself.

My latest attempt :

=if($b2="pencils",(vlookup,$c2,'Sheet 1'!$A:$AP,2)if($b2<>"pencils",(vlookup,$c2,'Sheet 2'!$A:$FJ,49))

Thanks In advance for any help.

**vlady** · 06-30-2013, 09:29 PM

you have only two condition..

=if($B2="pencils",vlookup(C2,$A:$B,2),vlookup(C2,$A:$AF,49))

**Ace_XL** · 07-01-2013, 01:56 AM

=vlookup(C2,$A:$AF,if($B2="pencils",2,49))

**benishiryo** · 07-01-2013, 03:30 AM

hmmmm. i thought OP's VLOOKUP are in 2 different sheets? Column A:AF also does not have 49 columns.

@JoJoGrl:
i suggest you upload a sample Excel file & show us 2 scenarios of answers. 1 is when B2 is "Pencil". tell us which cell should be the result. another is when B2 is not "Pencial". tell us what should be the result. i'm guessing:
=IF($B2="pencils",VLOOKUP($C2,'Sheet 1'!$A:$B,2),VLOOKUP($C2,'Sheet 2'!$A:$AW,49))

or:
=IF($B2="pencils",VLOOKUP($C2,'Sheet 1'!$A:$B,2,0),VLOOKUP($C2,'Sheet 2'!$A:$AW,49,0))

i think we are confused by the portion in red:
=if($b2="pencils",(vlookup,$c2,'Sheet 1'!$A:$AP,2)if($b2<>"pencils",(vlookup,$c2,'Sheet 2'!$A:$FJ,49))
- why range up A:AP when you only need the 2nd column?
- why range up A:FJ when you only need the 49th column?
- is the ID number for Sheet 1 & 2 both in column A?

to attach a file, Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**JoJoGrl** · 07-06-2013, 10:29 AM

Hello Everyone! Thanks so much for the feedback and I'm so sorry its been so long since I've checked in. I've been extremely ill and this is the first time I've been able to check in.

I'm pretty sure the first response is exactly what I used the first time I tried it....but I don't have access to try, verify, or post examples right now. My first chance to do so I will. I just didn't want everyone to think I didn't appreciate them.

Thanks! I will follow up.