+ Reply to Thread
Results 1 to 9 of 9

Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

  1. #1
    Registered User
    Join Date
    08-23-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    4

    Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    I am trying to work with the attached formula but keep getting NA

    The principle of the formula checks out so I can only assume I am inputting it wrong

    Anyone any ideas?

    =(IF($L23>1,-ParametersAFP!$K$68*VLOOKUP($B23-2,$B$7:$HL$133,215,0),0))
    +(IF($K23>1,-ParametersAFP!$K$66*VLOOKUP($B23-20,$B$7:$HL$133,215,0),0))
    +(IF($J23>1,-ParametersAFP!$K$63*VLOOKUP($B23-200,$B$7:$HL$133,215,0),0))
    +(IF($I23>1,-ParametersAFP!$K$59*VLOOKUP($B23-2000,$B$7:$HL$133,215,0),0))
    +(IF($L23+$k23>1,-ParametersAFP!$K$67*VLOOKUP($B23-11,$B$7:$HL$133,215,0),0))
    +(IF($L23+$j23>1,-ParametersAFP!$K$65*VLOOKUP($B23-101,$B$7:$HL$133,215,0),0))
    +(IF($j23+$k23>1,-ParametersAFP!$K$64*VLOOKUP($B23-110,$B$7:$HL$133,215,0),0))
    +(IF($i23+$l23>1,-ParametersAFP!$K$62*VLOOKUP($B23-1001,$B$7:$HL$133,215,0),0))
    +(IF($i23+$k23>1,-ParametersAFP!$K$61*VLOOKUP($B23-1010,$B$7:$HL$133,215,0),0))
    +(IF($i23+$j23>1,-ParametersAFP!$K$60*VLOOKUP($B23-1100,$B$7:$HL$133,215,0),0))


    Much appreciated
    Last edited by fitzgep; 08-23-2010 at 03:45 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    Should either the lookup value, B23 or the $B$7:$HL$133 be preceded by a reference to another sheet.. because B23 would be inside the range you are looking up in... which doesn't seem to make sense?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-23-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    no, no other sheet involved. Its looking up B23 minus a balue. The column B contains the values within which to search. The values relate to combinations of products (of which N23 is one) and so b23 minus the value will equal another product combination

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    But B23 is in the middle of the lookup table... why would you use that to get that value to lookup in the same table...?

    Anyway, perhaps select B7:B133 and then go to Data|Text to Columns and just click Finish.

    If that doesn't work.. make sure you have exact match... i.e. that you are not looking up a number that has multiple decimals (perhaps hidden by formatting) against values with differing number of decimals...

  5. #5
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    Also (apologies if this is a stupid question) you do know that VLOOKUP only looks up a value in the left-hand column of the table, don't you? If it doesn't find the value in that column it will return a #N/A

  6. #6
    Registered User
    Join Date
    08-23-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    its not looking up the value in B23 but B23 minus a combination of other values. The text to columns didnt work When you say exact match, between which values those in column B and those in index 215?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    I mean between B23-2 or 202 or whatever and what is in column B.

    Are they all whole numbers when formatted as General?

  8. #8
    Registered User
    Join Date
    08-23-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    no they are not both whole numbers but that does not stop a similar formula working in anither cell. The following uses the same logic and works fine

    =-(VLOOKUP($B9+1,$B$7:$GB$133,17,0)*VLOOKUP($B9+1,$B$7:$GB$133,179+COLUMN(GJ$8)-COLUMN($GI$8),0)+VLOOKUP($B9+10,$B$7:$GB$133,16,0)*VLOOKUP($B9+10,$B$7:$GB$133,179+COLUMN(GJ$8)-COLUMN($GI$8),0)+VLOOKUP($B9+100,$B$7:$GB$133,15,0)*VLOOKUP($B9+100,$B$7:$GB$133,179+COLUMN(GJ$8)-COLUMN($GI$8),0)+VLOOKUP($B9+1000,$B$7:$GB$133,14,0)*VLOOKUP($B9+1000,$B$7:$GB$133,179+COLUMN(GJ$8)-COLUMN($GI$8),0))

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Combining numerous IF and Vlookup function is resulting in NA and I cant see why?

    Use the Evaluate button on the Formula Auditing toolbar to igure out which expression is the problem.
    Entia non sunt multiplicanda sine necessitate

+ 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