+ Reply to Thread
Results 1 to 5 of 5

I need to return one of three answers in a cell

  1. #1
    Kath
    Guest

    I need to return one of three answers in a cell

    I need to have one of three results show up in the cell.

    I am using VLOOKUP to get my data result, but I need the cell to display
    "unknown" if the requested data is not valid. However to add to that, I need
    the cell to display nothing at all if there is no data being requested.

    Does anyone have any suggestions please?

  2. #2
    Ardus Petus
    Guest

    Re: I need to return one of three answers in a cell

    Say your lookup table is in D1:E10

    =IF(A1="","",IF(ISNA(MATCH(A1,D1:D10)),"unknown",VLOOKUP(A1:D1:E10,2,0)))

    HTH
    --
    AP

    "Kath" <Kath@discussions.microsoft.com> a écrit dans le message de news:
    8D5A5140-3D91-41A9-8BD5-5FB902B467A6@microsoft.com...
    >I need to have one of three results show up in the cell.
    >
    > I am using VLOOKUP to get my data result, but I need the cell to display
    > "unknown" if the requested data is not valid. However to add to that, I
    > need
    > the cell to display nothing at all if there is no data being requested.
    >
    > Does anyone have any suggestions please?




  3. #3
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    Can you give us a little more information. Unknown can be added to a lookup as the valure to return at he top or bottom of the range

    of

    if(isblank(a1)," ",if(vlookup you have used=#n/a,"Unknown",vlookup you have used))

    Regards

    Dav

  4. #4
    Kath
    Guest

    Re: I need to return one of three answers in a cell

    Thanks. I have used the formula now of

    =IF(ISBLANK(D19),"",IF(ISERROR(VLOOKUP(D19,Catalogue!$A$4:$C$42,2,FALSE)),"unknown",VLOOKUP(D19,Catalogue!$A$4:$C$42,2,FALSE)))

    and it returns the correct results, but it is saying it is an inconsistant
    formula.

    Any ideas on that?

    Thanks again
    Kath

    "Dav" wrote:

    >
    > Can you give us a little more information. Unknown can be added to a
    > lookup as the valure to return at he top or bottom of the range
    >
    > of
    >
    > if(isblank(a1)," ",if(vlookup you have used=#n/a,"Unknown",vlookup you
    > have used))
    >
    > Regards
    >
    > Dav
    >
    >
    > --
    > Dav
    > ------------------------------------------------------------------------
    > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
    > View this thread: http://www.excelforum.com/showthread...hreadid=557777
    >
    >


  5. #5
    Kath
    Guest

    Re: I need to return one of three answers in a cell

    My mistake - not an inconsistant formula - just an error of refering to empty
    cells - easily fixed by turning that error checker off.

    thanks very much Dav and Ardus Petus. Greatly appreciated.

    Kath

    "Kath" wrote:

    > Thanks. I have used the formula now of
    >
    > =IF(ISBLANK(D19),"",IF(ISERROR(VLOOKUP(D19,Catalogue!$A$4:$C$42,2,FALSE)),"unknown",VLOOKUP(D19,Catalogue!$A$4:$C$42,2,FALSE)))
    >
    > and it returns the correct results, but it is saying it is an inconsistant
    > formula.
    >
    > Any ideas on that?
    >
    > Thanks again
    > Kath
    >
    > "Dav" wrote:
    >
    > >
    > > Can you give us a little more information. Unknown can be added to a
    > > lookup as the valure to return at he top or bottom of the range
    > >
    > > of
    > >
    > > if(isblank(a1)," ",if(vlookup you have used=#n/a,"Unknown",vlookup you
    > > have used))
    > >
    > > Regards
    > >
    > > Dav
    > >
    > >
    > > --
    > > Dav
    > > ------------------------------------------------------------------------
    > > Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
    > > View this thread: http://www.excelforum.com/showthread...hreadid=557777
    > >
    > >


+ 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