IF & VLOOKUP functions

Z

zarealshook

New Member
Joined
May 26, 2014
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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.00
26/04/2022SAR98,625.00
31/10/2022EGY140,000.00
31/07/2022AED40,120.00
01/05/2022USD28,000.00
17/09/2022EUR36,000.00
06/01/2023SDG1,853,000.00
18/05/2022GBP28,400.00
08/01/2023AED163,000.00

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

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

Any early help will be appreciated,

Zare
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
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:
Excel Formula: 
=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.
 
Upvote 0
Solution
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:
Excel Formula: 
=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.
Click to expand...
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
 
Upvote 0
Sufiyan97 said:
Bases on your request, formula is doing what you asked for.
Click to expand...
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:

1673346811067.png
 
Upvote 0
You have not translated the suggested formula correctly
1673347952709.png

That is not reading the Currency it is reading the PO Amount so it will never = "USD". It needs to be =IF([@CURRENCY]="USD"
 
Upvote 0
I came up with this:
Book1
ABCDEFGH
1PO DATECURRENCYPO AMOUNTPO AMOUNT USD
215/01/2022SDG5,250,0003,060,750,000.00
322/07/2022SDG155,70090,500,625.00
426/04/2022SAR98,625370,682.06
531/10/2022EGY140,0003,805,324.60
631/07/2022AED40,120147,340.70
701/05/2022USD28,00028,000.00
817/09/2022EUR36,00033,247.80
906/01/2023SDG1,853,0001,085,913,590.00
1018/05/2022GBP28,40023,700.08
1108/01/2023AED163,000598,617.50
12
13
14DateUSDSDGEURGBPAEDSAREGY
15Jan-221583.000.937920.820903.67253.758527.18199
16Feb-221582.800.936120.828803.67253.758527.18187
17Mar-221581.000.934320.830703.67253.758527.18175
18Apr-221580.900.932530.832613.67253.758527.18162
19May-221588.890.930730.834513.67253.758527.18150
20Jun-221583.500.928930.836413.67253.758527.18138
21Jul-221581.250.927140.838323.67253.758527.18126
22Aug-221587.630.925340.840223.67253.758527.18113
23Sep-221584.120.923550.842123.67253.758527.18101
24Oct-221586.870.921750.844023.67253.758527.18089
25Nov-221584.660.919950.845933.67253.758527.18076
26Dec-221585.760.918160.847833.67253.758527.18064
27Jan-231586.030.916360.849733.67253.758527.18052
Sheet2
Cell Formulas
RangeFormula
D2: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
Excel Formula: 
DATEVALUE(MID(A2,4,2)&"/1/"&RIGHT(A2,4))
in the formula to:
Excel Formula: 
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.
 
Upvote 0
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"
Click to expand...
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
 
Upvote 0
jdellasala said:
I came up with this:
Book1
ABCDEFGH
1PO DATECURRENCYPO AMOUNTPO AMOUNT USD
215/01/2022SDG5,250,0003,060,750,000.00
322/07/2022SDG155,70090,500,625.00
426/04/2022SAR98,625370,682.06
531/10/2022EGY140,0003,805,324.60
631/07/2022AED40,120147,340.70
701/05/2022USD28,00028,000.00
817/09/2022EUR36,00033,247.80
906/01/2023SDG1,853,0001,085,913,590.00
1018/05/2022GBP28,40023,700.08
1108/01/2023AED163,000598,617.50
12
13
14DateUSDSDGEURGBPAEDSAREGY
15Jan-221583.000.937920.820903.67253.758527.18199
16Feb-221582.800.936120.828803.67253.758527.18187
17Mar-221581.000.934320.830703.67253.758527.18175
18Apr-221580.900.932530.832613.67253.758527.18162
19May-221588.890.930730.834513.67253.758527.18150
20Jun-221583.500.928930.836413.67253.758527.18138
21Jul-221581.250.927140.838323.67253.758527.18126
22Aug-221587.630.925340.840223.67253.758527.18113
23Sep-221584.120.923550.842123.67253.758527.18101
24Oct-221586.870.921750.844023.67253.758527.18089
25Nov-221584.660.919950.845933.67253.758527.18076
26Dec-221585.760.918160.847833.67253.758527.18064
27Jan-231586.030.916360.849733.67253.758527.18052
Sheet2
Cell Formulas
RangeFormula
D2: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
Excel Formula: 
DATEVALUE(MID(A2,4,2)&"/1/"&RIGHT(A2,4))
in the formula to:
Excel Formula: 
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.
Click to expand...
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
 
Upvote 0
Glad you got it sorted. Thanks for letting us know. (y)
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Help with a lookup problem
Replies
3
Views
470
thisoldman
T
P
  • Question Question
Date and currency look up
2
Replies
10
Views
2K
PM1
P
P
  • Question Question
Exchange Rates
Replies
1
Views
607
murphm03
M
A
  • Question Question
What can you use better Match or the HLookup function in combination with Vlookup
2
Replies
10
Views
1K
allard jansen
A
T
  • Question Question
MACRO w/Calculation -- HELP!!
Replies
2
Views
781
tygrl510
T

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top