0

First of all, am sorry if this was already posted by someone else, I tried searching for hours now, but did not find something similar to what I have here. I am very newb on excel.

My problem is, I have data from 2000 to present, on two different commodities, Coffee and also USD vs BRL (American Dollar vs Brazilian currency). The Coffee market only works certain days (no weekends or holidays) while the currency exchange works 24/7. So I have found data for both, however there is way more data on the currency than there is on coffee, and I want to merge both together to make a two axis chart.

I made the following code: =VLOOKUP([Date],USDBRL,USDBRL[USD/BRL]) Where [DATE] is the table I have for coffee and I want the USDxBRL next to the corresponding date. USDBRL is the table where I have all the value for the currency exchange rate. USDBRL[USD/BRL] is the column with the currency exchange rate

I am getting the following results: 36528 (equals to January, 03, 2000) on the first line, and it's the same date, but I should be getting 1.8055, which is the rate that I want... and the lines below are all the same +1 (increasing dates)

"Funny" part is that, if I scroll down a little bit more (to August 23, 2001) I start getting the actual results that I want, it is then showing the currency exchange rate that I need.

How can I copy the correct data? I uploaded a small version of my excel here.

I thank you in advance if you can help me out with this, been banging my head for hours now.

1 Answer 1

0

A problem with VLOOKUP is that, by default, it does not search for an exact match. To fix this, you need to set the range_lookup argument to FALSE.

Try using the following for C3 on the first sheet (assuming that's intended to be the USD/BRL rate for that date), and copy it down:

=VLOOKUP(A3,USDBRL!A:B,2,FALSE)

I don't generally use table references in my formulas, preferring explicit column/row identifiers instead, so you may want to modify the formula to suit your implementation better. Just remember to include that fourth argument and make it FALSE.

This formula should work with your tables as they are in the example workbook.

=VLOOKUP([@Date],USDxBRL[#All],2,FALSE)
4
  • Thanks, but I still get the same result 36528.00 (January, 03, 2000) It seems like it is copying the data from USDBRL!A instead of USDBRL!B Jul 16, 2014 at 20:02
  • It worked, I just had to not make it a table, thanks man!! :D Jul 16, 2014 at 20:06
  • @DanielWolthers I'm not sure why you were getting data from column A, if you were using the formula as I wrote it. The formula says, effectively "Find an exact match to A3 (on the current sheet) in the leftmost column of range A:B (on sheet USDBRL), and return the value from the second column in the matching row." - there's literally no way that Excel should be returning any values from column A of USDBRL with that formula.
    – Iszi
    Jul 16, 2014 at 20:34
  • @DanielWolthers I think I found your problem, and it also shows some probable other issues with your original formulas. See my updated post.
    – Iszi
    Jul 16, 2014 at 20:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .