# IF & VLOOKUP functions



## zarealshook (Monday at 6:22 PM)

Hi everyone, 

    First of all, I have installed the Add-In XL2BB but when I click on the MiniSheet, the Excel file gets hanging and then I need to close it and reopen again. This has happened for 4 times. 

     I have the following issue:

     If the "*Currency*" is "*USD*", keep the "*PO Amount*" as it is. If the "*Currency*" is anything else, then refer to the other sheet "*Forex*" and pull the corresponding exchange rate for the "*Currency*" to fill the column "*PO Amount USD*". The important thing is that it has to read the correct corresponding Month-Year exchange (January-2023) rate accordingly. 

*PO Data Sheet:*

PO DATECURRENCYPO AMOUNTPO AMOUNT USD 15/01/2022SDG5,250,000.00=IF("CURRENCY"="USD",PO Amount,

PO Amount/VLOOKUP,"CURRENCY",FOREX SHEET???
22/07/2022SDG155,700.0026/04/2022SAR98,625.0031/10/2022EGY140,000.0031/07/2022AED40,120.0001/05/2022USD28,000.0017/09/2022EUR36,000.0006/01/2023SDG1,853,000.0018/05/2022GBP28,400.0008/01/2023AED163,000.00

*FOREXT SHEET:*

DateUSDSDGEURGBPAEDSAREGYJanuary-20221.00583.000.937920.820903.67253.758527.18199February-20221.00582.800.936120.828803.67253.758527.18187March-20221.00581.000.934320.830703.67253.758527.18175April-20221.00580.900.932530.832613.67253.758527.18162May-20221.00588.890.930730.834513.67253.758527.18150June-20221.00583.500.928930.836413.67253.758527.18138July-20221.00581.250.927140.838323.67253.758527.18126August-20221.00587.630.925340.840223.67253.758527.18113September-20221.00584.120.923550.842123.67253.758527.18101October-20221.00586.870.921750.844023.67253.758527.18089November-20221.00584.660.919950.845933.67253.758527.18076December-20221.00585.760.918160.847833.67253.758527.18064January-20231.00586.030.916360.849733.67253.758527.18052February-20231.00March-20231.00

  I tried it with IF & VLOOKUP functions but I couldn't complete it properly. 

  Any early help will be appreciated,

  Zare


----------



## Z51 (Monday at 6:35 PM)

Assuming your dates in column A on both the PO Data and FOREX sheets are actual dates, just formatted to look like they do, then you can use:

```
=IF(B2="USD",C2,C2*INDEX(Forex!$C$3:$H$15,MATCH(DATE(YEAR(A2),MONTH(A2),1),Forex!$A$3:$A$15,0),MATCH(B2,Forex!$C$2:$H$2,0)))
```
If the "dates" on the FOREX sheet (column A) are text strings, my formula would need to be altered.


----------



## zarealshook (Tuesday at 5:17 AM)

Z51 said:


> Assuming your dates in column A on both the PO Data and FOREX sheets are actual dates, just formatted to look like they do, then you can use:
> 
> ```
> =IF(B2="USD",C2,C2*INDEX(Forex!$C$3:$H$15,MATCH(DATE(YEAR(A2),MONTH(A2),1),Forex!$A$3:$A$15,0),MATCH(B2,Forex!$C$2:$H$2,0)))
> ...


 Dear Z51, 

     Appreciated your early response. However, the formula you advised works perfectly on all currencies except "USD"!! I don't know why!! Anything I need to adjust? 

      Thanks for your quick reply. 

       Zare


----------



## Sufiyan97 (Tuesday at 5:23 AM)

zarealshook said:


> If the "*Currency*" is "*USD*", keep the "*PO Amount*" as it is.



Bases on your request, formula is doing what you asked for.


----------



## zarealshook (Tuesday at 5:33 AM)

Sufiyan97 said:


> Bases on your request, formula is doing what you asked for.


 Indeed it does, except when the currency "USD" is selected, it turns #N/A - Please see the below screenshot for your further reference and advice:


----------



## Peter_SSs (Tuesday at 5:53 AM)

You have not translated the suggested formula correctly



That is not reading the Currency it is reading the PO Amount so it will never = "USD". It needs to be =IF([@CURRENCY]="USD"


----------



## jdellasala (Tuesday at 7:10 AM)

I came up with this:
Book1ABCDEFGH1PO DATECURRENCYPO AMOUNTPO AMOUNT USD215/01/2022SDG5,250,0003,060,750,000.00322/07/2022SDG155,70090,500,625.00426/04/2022SAR98,625370,682.06531/10/2022EGY140,0003,805,324.60631/07/2022AED40,120147,340.70701/05/2022USD28,00028,000.00817/09/2022EUR36,00033,247.80906/01/2023SDG1,853,0001,085,913,590.001018/05/2022GBP28,40023,700.081108/01/2023AED163,000598,617.50121314DateUSDSDGEURGBPAEDSAREGY15Jan-221583.000.937920.820903.67253.758527.1819916Feb-221582.800.936120.828803.67253.758527.1818717Mar-221581.000.934320.830703.67253.758527.1817518Apr-221580.900.932530.832613.67253.758527.1816219May-221588.890.930730.834513.67253.758527.1815020Jun-221583.500.928930.836413.67253.758527.1813821Jul-221581.250.927140.838323.67253.758527.1812622Aug-221587.630.925340.840223.67253.758527.1811323Sep-221584.120.923550.842123.67253.758527.1810124Oct-221586.870.921750.844023.67253.758527.1808925Nov-221584.660.919950.845933.67253.758527.1807626Dec-221585.760.918160.847833.67253.758527.1806427Jan-231586.030.916360.849733.67253.758527.18052Sheet2Cell FormulasRangeFormulaD2:D11D2=IF(B2="USD",C2,ROUND(VLOOKUP(DATEVALUE(MID(A2,4,2)&"/1/"&RIGHT(A2,4)),$A$15:$H$27,MATCH(B2,$A$14:$H$14,0),FALSE)*C2,2))
Note that most of the dates in the PO DATE column (A) came in as text. If those are actual dates, you'll need to change

```
DATEVALUE(MID(A2,4,2)&"/1/"&RIGHT(A2,4))
```
in the formula to:

```
DATE(YEAR(A2),MONTH(A2),1)
```
 The dates in the FOREXT table came in as dates, and the first of the month which is what was needed.
I also used ROUND since this is money, and just using formatting to determine values will lead to incorrect values elsewhere.


----------



## zarealshook (Tuesday at 8:23 AM)

Peter_SSs said:


> You have not translated the suggested formula correctly
> View attachment 82427
> That is not reading the Currency it is reading the PO Amount so it will never = "USD". It needs to be =IF([@CURRENCY]="USD"


 Oh...!! How silly I am!! I can’t believe I did that! That was an epic fail! Thank you to all of you. I can now have peace of mind

  Keep up the good work. 

   Regards,

    Zare


----------



## zarealshook (Tuesday at 10:07 AM)

jdellasala said:


> I came up with this:
> Book1ABCDEFGH1PO DATECURRENCYPO AMOUNTPO AMOUNT USD215/01/2022SDG5,250,0003,060,750,000.00322/07/2022SDG155,70090,500,625.00426/04/2022SAR98,625370,682.06531/10/2022EGY140,0003,805,324.60631/07/2022AED40,120147,340.70701/05/2022USD28,00028,000.00817/09/2022EUR36,00033,247.80906/01/2023SDG1,853,0001,085,913,590.001018/05/2022GBP28,40023,700.081108/01/2023AED163,000598,617.50121314DateUSDSDGEURGBPAEDSAREGY15Jan-221583.000.937920.820903.67253.758527.1819916Feb-221582.800.936120.828803.67253.758527.1818717Mar-221581.000.934320.830703.67253.758527.1817518Apr-221580.900.932530.832613.67253.758527.1816219May-221588.890.930730.834513.67253.758527.1815020Jun-221583.500.928930.836413.67253.758527.1813821Jul-221581.250.927140.838323.67253.758527.1812622Aug-221587.630.925340.840223.67253.758527.1811323Sep-221584.120.923550.842123.67253.758527.1810124Oct-221586.870.921750.844023.67253.758527.1808925Nov-221584.660.919950.845933.67253.758527.1807626Dec-221585.760.918160.847833.67253.758527.1806427Jan-231586.030.916360.849733.67253.758527.18052Sheet2Cell FormulasRangeFormulaD2:D11D2=IF(B2="USD",C2,ROUND(VLOOKUP(DATEVALUE(MID(A2,4,2)&"/1/"&RIGHT(A2,4)),$A$15:$H$27,MATCH(B2,$A$14:$H$14,0),FALSE)*C2,2))
> Note that most of the dates in the PO DATE column (A) came in as text. If those are actual dates, you'll need to change
> 
> ...


    Thanks a lot for your help and support. I have used the earlier formula as it worked fine for me. I believe yours would also do the job. Appreciated your efforts. Thank you! 

      Kind Regards,

       Zare


----------



## Peter_SSs (Tuesday at 9:25 PM)

Glad you got it sorted. Thanks for letting us know.


----------

