IF statement with HLOOKUP (mulitple criteria) or INDEX/MATCH?

Ajwilltravel

New member
Joined
May 2, 2017
Messages
22
Reaction score
0
Points
0
Excel Version(s)
MS Office Prof Plus 2013
Hi there

In the attached file I need to input formulas in cells T10:T11 and U10:U11. The formulas in there currently are not working. So in T10 for example, if cell I10 = AUD then I need to lookup the table of rates in cell range Z1:AY5 for the relevant Period AND year (in cells D10 and G10) and multiply the value in cell K10 by the corresponding rate in the table in Z1:AY5 - 1st row of rates. In the row below the currency is GBP so I also need to include GBP in the formula (3rd line of rates in the table). I also need to include USD - much simpler as if I10 (and so on) = USD then I just have to pick up the value in L10 (the USD value).

This was working as an IF statement combined with HLOOKUP for 1st financial year but now that the data is crossing 2 financial years it is no longer working (as I have multiple criteria for the HLOOKUP - both the year and the period no.

I hope all this makes sense!

Thanks
Andrea.
 

Attachments

  • Formula 2.zip
    789.3 KB · Views: 12
The problem is that your search table doesn't have the joined year and date string in its first row. I've tried to upload a modified version of your sheet, but I'm getting an unexplained error.
My revised formula are:
=Z3&Z2 inserted in Z1 and copied across
=IF($I10="AUD",HLOOKUP($D10&$G10,$Z$1:$AX$9,4)*$K10,IF($I10="GBP",HLOOKUP($D10&$G10,$Z$1:$AX$9,6)*$K10,IF($I10="USD",$L10,NA()))) In T10

Hope that helps :)
 
I think I managed to upload the formula corrections :)
 

Attachments

  • Formula3.zip
    819.2 KB · Views: 11
Thanks very much for that, much appreciated. it's very weird tho.. your formula worked perfectly for most transactions except for the month 2017 10. I can see absolutely no reason for this! See example attached. you will see I changed some of the formula references slightly to suit my requirements.. any ideas why it wouldn't work just for that month? Your formula works for all the other months/years of data (the real spreadsheet is about 17k lines long), just not 2017 10.... I will send the attachment separately. Cheers :)
 
Hi
You are getting the wrong result for P10 2017 because you are not specifying range-look-up in your HLOOKUP expressions. If you don't do this, Excel uses TRUE as the default.
This means that Excel can return a value that is close to the Look-up Value, but it also means that the values in the first row must be in numerical order or it might not return a value as in this case. Below I have used row 11 as an example, and I've added the parameter FALSE in red. This directs Excel to return only an exact match, and the first row can be in any order.
If you want you can use 0 and 1 in place of FALSE and TRUE respectively.

=IF($I11="AUD",HLOOKUP($D11&$G11,$Z$1:$AX$9,4,FALSE)*$K11,IF($I11="GBP",HLOOKUP($D11&$G11,$Z$1:$AX$9,6,FALSE)*$K11,IF($I11="USD",$L11,NA()))) In T11
 
Last edited:
Back
Top