# Nested IF with a VLOOKUP?

1. ## Nested IF with a VLOOKUP?

I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
contain a store number. stores.xls also has gross sales listed in column F.

The formula will reside in sales.xls (column G for each row). What I need to
do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
it finds a match, I need it to return cell F [stores.xls] in the same row.

Because there are some cells that will be a null value, I need them to
return blank.

I was thinking that this would be a nested IF statement with a VLOOKUP, but
any ideas.

Thanks!

2. ## RE: Nested IF with a VLOOKUP?

hi Plunk,

=if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0))

hth
regards from Brazil

"plunk25" escreveu:

> I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
> contain a store number. stores.xls also has gross sales listed in column F.
>
> The formula will reside in sales.xls (column G for each row). What I need to
> do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
> it finds a match, I need it to return cell F [stores.xls] in the same row.
>
> Because there are some cells that will be a null value, I need them to
> return blank.
>
> I was thinking that this would be a nested IF statement with a VLOOKUP, but
> I wasn't sure if that was efficient or the only way. Please help if you have
> any ideas.
>
> Thanks!

3. ## Re: Nested IF with a VLOOKUP?

Hi,

=IF(ISERROR(VLOOKUP(.....)),"",VLOOKUP(.....))

If the function has some error in it, if will return an empty cell
otherwise the corresponding value for the VLOOKUPed value.

Thanks.

Shail

plunk25 wrote:

> I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
> contain a store number. stores.xls also has gross sales listed in column F.
>
> The formula will reside in sales.xls (column G for each row). What I need to
> do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
> it finds a match, I need it to return cell F [stores.xls] in the same row.
>
> Because there are some cells that will be a null value, I need them to
> return blank.
>
> I was thinking that this would be a nested IF statement with a VLOOKUP, but
> I wasn't sure if that was efficient or the only way. Please help if you have
> any ideas.
>
> Thanks!

4. ## Re: Nested IF with a VLOOKUP?

=IF(ISNA(VLOOKUP(A2,[stores.xls]Sheet1!\$C\$1000:\$F\$1000,4,FALSE)),"",VLOOKUP(
A2,[stores.xls]Sheet1!\$C\$1000:\$F\$1000,4,FALSE))

--
HTH

Bob Phillips

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

"plunk25" <plunk25@discussions.microsoft.com> wrote in message
news:20EF7E15-C0C4-43A0-87AB-FAE8D0F4B38C@microsoft.com...
> I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column

that
> contain a store number. stores.xls also has gross sales listed in column

F.
>
> The formula will reside in sales.xls (column G for each row). What I need

to
> do is compare [sales.xls]A2 to column C in stores.xls and find a match.

After
> it finds a match, I need it to return cell F [stores.xls] in the same row.
>
> Because there are some cells that will be a null value, I need them to
> return blank.
>
> I was thinking that this would be a nested IF statement with a VLOOKUP,

but

have
> any ideas.
>
> Thanks!

5. ## RE: Nested IF with a VLOOKUP?

Marcelo - I believe your vlookup's column index number should be 4:

=if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0))

--
Regards,
Dave

"Marcelo" wrote:

> hi Plunk,
>
> =if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0))
>
> hth
> regards from Brazil
>
>
>
> "plunk25" escreveu:
>
> > I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
> > contain a store number. stores.xls also has gross sales listed in column F.
> >
> > The formula will reside in sales.xls (column G for each row). What I need to
> > do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
> > it finds a match, I need it to return cell F [stores.xls] in the same row.
> >
> > Because there are some cells that will be a null value, I need them to
> > return blank.
> >
> > I was thinking that this would be a nested IF statement with a VLOOKUP, but
> > I wasn't sure if that was efficient or the only way. Please help if you have
> > any ideas.
> >
> > Thanks!

6. ## RE: Nested IF with a VLOOKUP?

yeah David, my mistake

regards

"David Billigmeier" escreveu:

> Marcelo - I believe your vlookup's column index number should be 4:
>
> =if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,4,0))
>
> --
> Regards,
> Dave
>
>
> "Marcelo" wrote:
>
> > hi Plunk,
> >
> > =if(iserror(vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0)),"",vlookup([sales.xls]sheet1!a2,[stores.xls]sheet1!c2:f1000,3,0))
> >
> > hth
> > regards from Brazil
> >
> >
> >
> > "plunk25" escreveu:
> >
> > > I have 2 workbooks: stores.xls and sales.xls. Both sheets have a column that
> > > contain a store number. stores.xls also has gross sales listed in column F.
> > >
> > > The formula will reside in sales.xls (column G for each row). What I need to
> > > do is compare [sales.xls]A2 to column C in stores.xls and find a match. After
> > > it finds a match, I need it to return cell F [stores.xls] in the same row.
> > >
> > > Because there are some cells that will be a null value, I need them to
> > > return blank.
> > >
> > > I was thinking that this would be a nested IF statement with a VLOOKUP, but
> > > I wasn't sure if that was efficient or the only way. Please help if you have
> > > any ideas.
> > >
> > > Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1