+ Reply to Thread
Results 1 to 6 of 6

Nested IF with a VLOOKUP?

  1. #1
    plunk25
    Guest

    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
    I wasn't sure if that was efficient or the only way. Please help if you have
    any ideas.

    Thanks!

  2. #2
    Marcelo
    Guest

    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. #3
    shail
    Guest

    Re: Nested IF with a VLOOKUP?

    Hi,

    This might help you:

    =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. #4
    Bob Phillips
    Guest

    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
    > I wasn't sure if that was efficient or the only way. Please help if you

    have
    > any ideas.
    >
    > Thanks!




  5. #5
    David Billigmeier
    Guest

    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. #6
    Marcelo
    Guest

    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!


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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