VLOOKUP formula with #N/A - don't know where is the problem...

[yesitsmewojtek]

Hi guys,

I'm using the lookup formula to look up values in array. I'm looking for a specific exchange rate for a specific date and currency.
Cell B61 contains EUR, cell B62 contains 3/9/2018 (formatted and showed as date 09/03/2018).
Below VLOOKUP works perfectly:

=VLOOKUP(B62,A4:AJ55,MATCH(1&" "&B61,A1:AJ1,)) always for each currency and date in my array (the result is 4.1989).

I don't understand why I'm getting the #N/A error for formula like this:

=VLOOKUP(("3/9/2018"),A4:AJ55,MATCH(1&" "&("USD"),A1:AJ1,))

A4:AJ55 is a table_array. In first column contains dates [e.g. 3/8/2018 (showed and formatted as date 08/03/2018)]. lookup_value in MATCH formula requires 1&" "& because I'm looking up in values like 1 USD.
Can I put exact values in VLOOKUP formula (like USD or 3/9/2018)? I have to understand that.

 

[Joe4]

Welcome to the Board!

Dates are actually stored in Excel as Numbers, specifically the number of days since 1/0/1900. To see this, go to any date and change the format to General.
"3/9/2018" is NOT a date or numeric entry, it is a Text entry. You cannot compare Text to Numbers/Date.
Instead of "3/9/2018", use the DATE function, i.e. DATE(2019,3,9).

 

[yesitsmewojtek]

Thanks Joe4. I forgot about that. Now it works fine.

 

[Joe4]

You are welcome.

 

[yesitsmewojtek]

OK, not everything is fine.

This is a part of VBA code that I wrote. Active cells are in Sheet10:

Private Sub InvoiceDateTextBox_AfterUpdate()

ActiveCell = InvoiceDateTextBox.Value

Dim ID As Date
Dim CV As String
CV = ActiveCell.Offset(0, -1).Value (the value of cell could be PLN, EUR, USD)

If CV = "PLN" Then
ActiveCell.Offset(0, 1).Select

Else

ID = ActiveCell.Value
ActiveCell.Offset(0, 1).Select

ActiveCell.Formula = _
"=VLOOKUP((" & ID & "),Sheet12!A4:AJ55,MATCH(1&"" ""&(" & CV & "),Sheet12!A1:AJ1,))"

End If

ActiveCell.Offset(0, 1).Select

End Sub

The ActiveCell.Formula gives =VLOOKUP((3/13/2018),Sheet12!A4:AJ55,MATCH(1&" "&(USD),Sheet12!A1:AJ1,))This part of code =VLOOKUP((" & ID & ") which gives (3/13/2018) is a look_up value (whole array is in other worksheet). This value should be a specific date and it is assigned to ID dim from a cell (this date can be different - is entered to a cell from TextBox, the cell is formated as date, for example 13/03/2018). I was trying to figure out how to use Date(year, month, day) function instead of (" & ID & "). I can't just simply make something like that: Date(" & ID & "). Is there any other option to get a date from a cell and put it into VLOOKUP as a look_up value (to look up on it in other sheet)?

Also part MATCH(1&" "&(USD),Sheet12!A1:AJ1,) should look like that: MATCH(1&" "&("USD"),Sheet12!A1:AJ1,)
I don't know how to add quotation marks around USD. I was trying to add them in many ways but none of them works.
The formula works properly when I'm using Date function and when the CV dim is in quote marks.

 

[Joe4]

We can use DATEVALUE to convert it from Text to Date.

The issue is determining the literal double-quotes from the double-quotes used as text qualifiers. You can double/triple them up to get them to work, but to me that looks messy and confusing. I prefer to use Chr(34) instead, which is the ASCII character for double-quotes.

So, I think the formula should look something like:

    ActiveCell.Formula = _
        "=VLOOKUP(DATEVALUE(" & Chr(34) & ID & Chr(34) & "),Sheet12!A4:AJ55,MATCH(1&"" ""&(" & Chr(34) & CV & Chr(34) & "),Sheet12!A1:AJ1,))"

 

[yesitsmewojtek]

Didn't know about all that stuff that you mentioned. Now the formula works as it should. Thanks.

 

[Joe4]

You are welcome!