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

1. ## 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

2. ## 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?

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

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

#### 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