+ Reply to Thread
Results 1 to 3 of 3

VLookup returns #VALUE!

  1. #1
    BEEJAY
    Guest

    VLookup returns #VALUE!

    Excel 2003;
    The following returns #VALUE!, which interferes with =SUM

    =VLOOKUP(E7,'L-Ups'!$B$8:$E$23,2,FALSE)
    The message is:" A value used in the formula is the wrong data type"
    I have taken all data, multiplied by 1, copy and paste special as value into
    tables.
    I have verified that cell E7 on the "L-Ups" Sheet is a number.
    I'm stuck.
    Any suggestions?


  2. #2
    Ken Wright
    Guest

    Re: VLookup returns #VALUE!

    E7 is a number, so =ISTEXT(E7) = FALSE yes?

    If you do the same to the 'number' in your table, does it also return FALSE?

    Assuming for example, the matching number to E7 is in cell B12, if you do
    =E7=B12 you get TRUE, yes?

    --
    Regards
    Ken....................... Microsoft MVP - Excel
    Sys Spec - Win XP Pro / XL 97/00/02/03

    ------------------------------*------------------------------*----------------
    It's easier to beg forgiveness than ask permission :-)
    ------------------------------*------------------------------*----------------


    "BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
    news:21112AD8-8D6F-46F7-9A16-F2290C482634@microsoft.com...
    > Excel 2003;
    > The following returns #VALUE!, which interferes with =SUM
    >
    > =VLOOKUP(E7,'L-Ups'!$B$8:$E$23,2,FALSE)
    > The message is:" A value used in the formula is the wrong data type"
    > I have taken all data, multiplied by 1, copy and paste special as value
    > into
    > tables.
    > I have verified that cell E7 on the "L-Ups" Sheet is a number.
    > I'm stuck.
    > Any suggestions?
    >




  3. #3
    BEEJAY
    Guest

    Re: VLookup returns #VALUE!

    Thanks for your info.
    This helped me think in other directions.
    Cell E7 is 'empty' when it gives me the #VALUE! error
    As soon as I put in a valid number that corresponds to the lookup chart
    the error disappears.
    I got around the problem by adding a new row to the chart with only zero's
    in it.
    Now it does not give me an error, even when I have NO number in E7.
    Go figure.
    Thanks much for your input.

    "Ken Wright" wrote:

    > E7 is a number, so =ISTEXT(E7) = FALSE yes?
    >
    > If you do the same to the 'number' in your table, does it also return FALSE?
    >
    > Assuming for example, the matching number to E7 is in cell B12, if you do
    > =E7=B12 you get TRUE, yes?
    >
    > --
    > Regards
    > Ken....................... Microsoft MVP - Excel
    > Sys Spec - Win XP Pro / XL 97/00/02/03
    >
    > ------------------------------*------------------------------*----------------
    > It's easier to beg forgiveness than ask permission :-)
    > ------------------------------*------------------------------*----------------
    >
    >
    > "BEEJAY" <BEEJAY@discussions.microsoft.com> wrote in message
    > news:21112AD8-8D6F-46F7-9A16-F2290C482634@microsoft.com...
    > > Excel 2003;
    > > The following returns #VALUE!, which interferes with =SUM
    > >
    > > =VLOOKUP(E7,'L-Ups'!$B$8:$E$23,2,FALSE)
    > > The message is:" A value used in the formula is the wrong data type"
    > > I have taken all data, multiplied by 1, copy and paste special as value
    > > into
    > > tables.
    > > I have verified that cell E7 on the "L-Ups" Sheet is a number.
    > > I'm stuck.
    > > Any suggestions?
    > >

    >
    >
    >


+ 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