VLookUp plus IF function

MissJoe

New Member
Joined
May 8, 2013
Messages
7
Hi everyone, I'm new here but I've got lots of solutions from this forum before. Now I'm here with a request for your help.

I have two data sheets in excel with info as below.

Sheet 1
AccountDescriptionCost CenterBeginning BalanceDebitCreditEnding Balance
1000000FurnitureXX022,0001,5003003,200
2000000CashXX0410,00012,0005,60016,400

<tbody>
</tbody>

Sheet 2
AccountDescriptionCategory 1Category 2Category 3Active?
1000000FurnitureBalance SheetNon-Cur AssetProperty & Equipment
2000000CashBalance SheetCurrent AssetCash & Bank Balances

<tbody>
</tbody>

What I would like to do is, if the Value in Sheet 2 Column 1 matches the value in Sheet 1 and if the value in sheet 1 column 7 (ending balance) is greater than 0, I want it to bring out "Yes" in Sheet 1 Column 6 (Active?)
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Hi everyone, I'm new here but I've got lots of solutions from this forum before. Now I'm here with a request for your help.

I have two data sheets in excel with info as below.

Sheet 1
Account
Description
Cost Center
Beginning Balance
Debit
Credit
Ending Balance
1000000
Furniture
XX02
2,000
1,500
300
3,200
2000000
Cash
XX04
10,000
12,000
5,600
16,400

<TBODY>
</TBODY>

Sheet 2
Account
Description
Category 1
Category 2
Category 3
Active?
1000000
Furniture
Balance Sheet
Non-Cur Asset
Property & Equipment
2000000
Cash
Balance Sheet
Current Asset
Cash & Bank Balances

<TBODY>
</TBODY>

What I would like to do is, if the Value in Sheet 2 Column 1 matches the value in Sheet 1 and if the value in sheet 1 column 7 (ending balance) is greater than 0, I want it to bring out "Yes" in Sheet 1 Column 6 (Active?)


Hello and welcome
Did you mean "Active" in Sheet 2?
If such try:
=IF(VLOOKUP(A2,Sheet1!$A$2:$G$3,7,0)>0,"yes","no")
 

MissJoe

New Member
Joined
May 8, 2013
Messages
7
Hi Robert,

Thank you for responding.

However, the formula doesn't seem to work. If I may to clarify the question, the data that should result in Sheet 2 Column F2 should look up and match the account numbers in Sheet 2 with Sheet 1 and if they're a match then check if the Ending Balance (Sheet 1 Column G2) then if the balance is greater than 1, then I want it to read "Yes". If not, then "No".
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Hi Robert,

Thank you for responding.

However, the formula doesn't seem to work. If I may to clarify the question, the data that should result in Sheet 2 Column F2 should look up and match the account numbers in Sheet 2 with Sheet 1 and if they're a match then check if the Ending Balance (Sheet 1 Column G2) then if the balance is greater than 1, then I want it to read "Yes". If not, then "No".

Whcih part does not work?
Are you gettin errors?

GReater than 0(your first post) or 1 your second post?
=IF(VLOOKUP(A2,Sheet1!$A$2:$G$3,7,0)>1,"yes","no")
 

MissJoe

New Member
Joined
May 8, 2013
Messages
7
I'm sorry. I'll stick with greater than zero.

The formula works! Thanks a loooot.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,413
Messages
5,595,998
Members
414,037
Latest member
Roamingsmile

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
Top