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

Transaction Date Transaction original currency Amount in original currency Relevant exchange rate Amount in GBP 30/06/2012 USD 325.90 1.55914 209.03 30/06/2012 USD 228.78 1.55914 146.74 30/06/2012 USD 124.79 1.55914 80.04 <colgroup><col><col><col><col span="2"></colgroup><tbody> </tbody>

<tbody>
</tbody>

to select the relevant exchange rate the VLookup combines transaction original currency date and the array with currencies and dates vertically

01/01/2012 01/02/2012 01/03/2012 01/04/2012 12012 22012 32012 42012 1 2 3 4 5 6 Exchange rates Jan-2012 Feb-2012 Mar-2012 Apr-2012 SSP GBP 4.32396 4.39915 4.45016 4.45352 USD GBP 1.54175 1.56856 1.58674 1.58794 <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

[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!