Help with Excel EXP formula with Vlookup and IF function spitting #N/A

[CocoZhou]

I am trying to calculate a formula using EXP( ), in it, I use the IF function and the Vlookup function. I have tried it in different ways and I think the issue lies within the VLookup function. I have the dates available within the range of my VLookup in the other sheet (Input Market Data (2) ), but somehow it keeps spitting out #N/A. 

In the images, the column of dates is from "Input Market Data (2)" and the Formula is the one that keeps spitting out #N/A

  

[#2]

The image is really too small for my tired eyes, and too blurry when I try to enlarge it. It is always better to attach an Excel file, not an image. And that is probably even more true for this particular question.

I'll bet the problem has to do with VLOOKUP, not EXP. Simply extract the VLOOKUP part into a separate cell to confirm.

I think your last VLOOKUP parameter is zero (FALSE). So you are looking for an exact match. And VLOOKUP fails to find one. That should be easy to confirm.

If the lookup value (C8 ?) is numeric, the problem might be that C8 and/or the values in A6:A20 (?) are calculated, and their __binary__ values do not match. That could be confirmed with MATCH(C8, Ax, 0). Caution: C8=Ax might return true if their values rounded to 15 significant digits match.

Alternatively, what appears to be numeric might, in fact, be text.  Looks can be deceiving; and the format of the cells does not matter.  Use the ISTEXT or ISNUMBER function to confirm.

Beyond that, we cannot say what is happening unless: (a) you provide an Excel file that demonstrates the problem: or (b) you display all calculated values formatted as Number with 15 significant digits.  But even the latter might not be sufficient.

[#3]

thank you so much for your help, I've tried it and it's definitely a Vlookup issue, but I'm not sure how to fix it. I have the excel document and I marked the area where it goes wrong, but not sure how to attach it

[#4]

just attached it to the original post!

[#5]

@CocoZhou You need to change the lookup range so that the first column holds the values you would like to find a match for. So, in E8, change the formula to:

=VLOOKUP($C8,'Input market data IR (2)'!$B$6:$L$60,9,0)

Similar for the formulae in F and G. Start the lookup range in column B and decrease the column count by 1.

[#6 · Accepted solution]

@Riny_van_Eekelen  wrote: ``change the formula to
=VLOOKUP($C8,'Input market data IR (2)'!$B$6:$L$60,9,0)
Start the lookup range in column B and decrease the column count by 1``

I agree with Riny insofar as changing the lookup column to B instead of A.

But the additional adjustment to the formula in G8 is more than merely decreasing the column offset by 1.

I think it would be easier to understand and maintain if you change the formulas to use an INDEX/MATCH paradigm.  Specifically:

E8:

=INDEX('Input market data IR (2)'!$J$6:$J$60,
MATCH($C8,'Input market data IR (2)'!$B$6:$B$60,0))

F8:

=INDEX('Input market data IR (2)'!$K$6:$K$60,
MATCH($C8,'Input market data IR (2)'!$B$6:$B$60,0))

G8:

=IF($A8<$C$5,0,EXP(-$B8*(E8+($B8
-INDEX('Input market data IR (2)'!$I$6:$I$60,
MATCH($C8,'Input market data IR (2)'!$B$6:$B$60,0)))*F8)))

But note that you still get #N/A errors in row 82 because C82 is 12/31/2023 instead of 12/1/2023.

You might fix C82.

But I wonder if you really want MATCH(...,...,1) instead of MATCH(...,...,0).

The change presumes that the dates in B6:B60 are in ascending order.  That is true for the Excel file that you posted.  Only you can say whether we can depend on that.

[#7]

thank you, it worked!

[#8]

thank you so much, this was very helpful!