0

I'ven been trying to use the IF and VLOOKUP functions to trigger a formula based on what is in a particular row.

The first row contains how much is due. The second row contains one of five possible original amount (115, 95, 65, 45, 35).

If the cell contains one of those original amounts, I need it to trigger the correct formula, which would return one of two answers, the "original amount" (in number format) or "8.50."

The "Due" will either be zero or a value equal or greater to the "Org$" - if it doesn't then an error message should be returned.

For example,

Due    Org$    Answer I need formula return
0.00    45     45
65.00   65     8.50
115     35     8.50
0       115    115

if the Org$ is one of those five values, then it would trigger a formula that would determine if that org$ is equal to zero then it produces that original amount, if it is not equal to zero then it produces "8.50" - if a number in the org$ is not one of the five then "ERROR" should show. Or if the value of "Due" is less than the Org$, then "ERROR" should be the result.

I tried using an IF string (=IF(B2=115,$J$4,IF(B2=95,$J$5,IF(B2=65,$J$6,IF(B2=45,$J$7,IF(B2=35,$J$8, "ERROR"))))) - where the $J$4 is the following in a table: =IF(B2<=A2, 8.5,115)

When I use this, the first one comes out correctly, but all Org$ below it return the answer of the Org$ regardless of what is in "Due"

Any suggestions? been at this for two days (also tried using vlookup combined with if function.

0

You can use the following:

=IF(ISNUMBER(MATCH(B2,{115,95,65,45,35},0)),IF(B2<=A2,8.5,B2),"Error")

Match will compare the value in org$ to the five original amount (115, 95, 65, 45, 35)
If there is a match it will return a number then If will test B2<=A2
True means 8.5
False means the original amount in B2 (one of (115, 95, 65, 45, 35)).
In case of no match the formula will return "Error"
You can drag the formula down the column

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.