VLOOKUP PROBS!!! - Page 3
Thanks Thanks:  0
Likes Likes:  0
Page 3 of 3 FirstFirst 123
Results 21 to 22 of 22

Thread: VLOOKUP PROBS!!!

  1. #21
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    On 2002-03-05 15:56, cnickerson wrote:
    Yes Mark, I did enter it with Ctrl,Shift, and Enter, and came up with the same #VALUE! I know that because I tried entering it normally without any results. The REM.TXT is a file I open along with the regular worksheet (amongst other files). I don't think it's in the same workbook. When I open REM.TXT I have to set the columns etc, so everything lines up. But again, if there isn't a product code for VLOOKUP to find it returns #VALUE! I know I'm sayin the same thing over and over, but that's the case.
    Okay, let's break this problem down. But, 1st you must dismiss the notion that the formula is returning #VALUE! because it didn't find the lookup value in your table. Believe me (or at least believe the Excel Help topic for VLOOKUP worksheet function) that (when using the formula that I supplied, {=SUM(VLOOKUP(B10,REM.TXT!$B$1:$J$700,{5,7},0)*{1,-1})} ) only a #N/A error is returned when no match is found. Since a #N/A isn't being returned then either VLOOKUP is finding #VALUE! in columns 5 and/or 7, or a text value (e.g., "cat", "red", "") that can't be coerced into a number is being returned.

    1. I noticed that your table range ($B$1:$J$700) includes row 1. Is row 1 the 1st entry in the table or headers that label your table columns?

    2. Enter the formulas, =ISTEXT(F1) and =ISTEXT(H1), into unused columns on row 1 and copy down to row 700. Do either of these formulas produce TRUE on any row? If so, on the formula bar select the cell reference within that ISTEXT function and report your finding in a reply posting.

    Let's start there and see what you find.

    [ This Message was edited by: Mark W. on 2002-03-06 06:57 ]

  2. #22
    New Member
    Join Date
    Mar 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks for your help Mike, defintely helped me out!!! The report I was using did have text so I used IFCOUNT to find if the code was there in the first place. Seems to work great now. I appreciate all the posts that helped me out. Here's the function I used if you are interested.

    =IF(COUNTIF(Rem.txt!$B$1:$J$700,B10)=1,SUM(VLOOKUP(B10,Rem.txt!$B$1:$J$700,{5,7},0)*{1,-1}),0)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com