What can you use better Match or the HLookup function in combination with Vlookup

[allard jansen]

Mike great as always, but tell me can you as well replace in work sheet were you use a combination vertical lookup and Match,use Horizontal lookup in stead of Match? What is the advantage of Match to HLookup which I use to select the correct exchange rate with its date , my example is here =IF(ISERROR(VLOOKUP(H4,$O$<wbr style="color: rgb(51, 51, 51); font-family: arial, sans-serif; font-size: 12.727272033691406px; line-height: 16px; background-color: rgba(255, 255, 255, 0.699219); ">*7:$AB$14,HLOOKUP(MONTH(G4)&YEA<wbr style="color: rgb(51, 51, 51); font-family: arial, sans-serif; font-size: 12.727272033691406px; line-height: 16px; background-color: rgba(255, 255, 255, 0.699219); ">*R(G4),$Q$3:$AB$4,2,1),FALSE)),<wbr style="color: rgb(51, 51, 51); font-family: arial, sans-serif; font-size: 12.727272033691406px; line-height: 16px; background-color: rgba(255, 255, 255, 0.699219); ">*" ",VLOOKUP(H4,$O$7:$AB$14,HLOOK<wbr style="color: rgb(51, 51, 51); font-family: arial, sans-serif; font-size: 12.727272033691406px; line-height: 16px; background-color: rgba(255, 255, 255, 0.699219); ">*UP(MONTH(G4)&YEAR(G4),$Q$3:$AB<wbr style="color: rgb(51, 51, 51); font-family: arial, sans-serif; font-size: 12.727272033691406px; line-height: 16px; background-color: rgba(255, 255, 255, 0.699219); ">*$4,2,1),FALSE))

I can send you the whole excel file were I use it or my client use it

allard
Just to give an idea of the problem
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 377"]
<colgroup><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Transaction Date[/TD]
[TD]Transaction original currency[/TD]
[TD]Amount in original currency [/TD]
[TD]Relevant exchange rate[/TD]
[TD]Amount in GBP[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2012[/TD]
[TD]USD[/TD]
[TD="align: right"]325.90[/TD]
[TD]1.55914[/TD]
[TD="align: right"]209.03[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2012[/TD]
[TD]USD[/TD]
[TD="align: right"]228.78[/TD]
[TD]1.55914[/TD]
[TD="align: right"]146.74[/TD]
[/TR]
[TR]
[TD="align: right"]30/06/2012[/TD]
[TD]USD[/TD]
[TD="align: right"]124.79[/TD]
[TD]1.55914[/TD]
[TD="align: right"]80.04[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
to select the relevant exchange rate the VLookup combines transaction original currency date and the array with currencies and dates vertically
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 479"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]01/01/2012[/TD]
[TD]01/02/2012[/TD]
[TD]01/03/2012[/TD]
[TD]01/04/2012[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]12012[/TD]
[TD]22012[/TD]
[TD]32012[/TD]
[TD]42012[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Exchange rates[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]Jan-2012[/TD]
[TD="align: right"]Feb-2012[/TD]
[TD="align: right"]Mar-2012[/TD]
[TD="align: right"]Apr-2012[/TD]
[/TR]
[TR]
[TD]SSP[/TD]
[TD]GBP[/TD]
[TD="align: right"]4.32396[/TD]
[TD="align: right"]4.39915[/TD]
[TD="align: right"]4.45016[/TD]
[TD="align: right"]4.45352[/TD]
[/TR]
[TR]
[TD]USD[/TD]
[TD]GBP[/TD]
[TD="align: right"]1.54175[/TD]
[TD="align: right"]1.56856[/TD]
[TD="align: right"]1.58674[/TD]
[TD="align: right"]1.58794[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[FONT=arial, sans-serif] and H lookup is for horizontally selecting month and year the right exchange rate in relevant exchange rate. Can Match do the same?[/FONT]

 

[allard jansen]

In post #4. Here it is again:

Optimising Excel Lookups - Decision Models

Andrew thanks for all your assistance. You guys are doing great work. No waiting J.I.T. service I would say.

Take care! Stay Safe!