# Formula Question...

#### andyinla

##### New Member
My General Ledger is an excel file generated by Finance and has a column with invoice numbers. My Cost Report is a separate excel file that also has an invoice column with invoice numbers

I want to do a formula that will place the month in GL, actual month "02/2009", if the invoice numbers both appear in both reports, General Ledger and Cost Report, so I don't have to go through the Generl Ledger line by line and check did invoice number 66758 apear on my GL for Feb and is that number in Cost Report as well..

How do I go about doing that formula?

Thank you so much

Andy

Last edited:

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### jasonb75

##### Well-known Member
Hi Andy.

This could be done with lookup and an error check, something like

=IF(ISERROR(VLOOKUP(\$A2,'COST REPORT'!\$A:\$A,1,FALSE)),"","02/2009")

\$A2 should be the cell with the invoice number in the current row.
\$A:\$A should be the column in the cost report that contains the invoice numbers.
'COST REPORT' should be the file and sheet name where the column above is found (if you leave it like it is, as long as there is not a sheet called COST REPORT in the GL workbook, excel should open a window asking you to select the file and sheet that it should be looking at).

Note this formula will always enter "02/2009" as the month, if the month should be entered as found elsewhere then it can be referenced by changing the formula.

#### andyinla

##### New Member
Jason

Thank you for your help, I think I am almost there...
here is what I have....

=IF(ISERROR(VLOOKUP(\$K423,'Cost Report'!\$A:\$A,1,FALSE)),'[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!\$G:\$G"","02/2009")

[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!\$G:\$G
this is reference to my General Ledger file and referencing the column with the invoice numbers

What do you think?

#### jasonb75

##### Well-known Member
Andy

Try

=IF(ISERROR(VLOOKUP(\$K423,'[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!\$G:\$G,1,FALSE)),"","02/2009")

the filename and sheet name '[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3' should be the opposite file to the one in which you enter the formula.

Replies
2
Views
346
Replies
0
Views
675
Replies
0
Views
469
Replies
0
Views
1K
Replies
0
Views
745

1,191,671
Messages
5,987,956
Members
440,121
Latest member
eravella

### 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.

### Which adblocker are you using?

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

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