Formula help to match columns and numbers

[zone709]

I have to sets of data to compare invoice number is column A and amount in B verse C & D . Answer in E

i have much more data then this going down the whole sheet. What i am trying to do in column E and need help with is a formula that if column A invoice number and column B on same line match column C and D to return zero. If it cant locate the invoice number in column A & C then return NA. which means its not found. As you can see if it finds A & B with C & D and the amount if off it return the difference. Can this be done in a formula or macro. Any help would be apricated.

Invoice number is column A to E


Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Invoice Number[/td][td] Amount [/td][td]Invoice Number [/td][td] Amount [/td][td] Diffrence [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]

25613​

[/td][td]

$ 600.00​

[/td][td]

25613​

[/td][td]

$ 750.00​

[/td][td]

$ (150.00)​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]

15623​

[/td][td]

$ 1,200.00​

[/td][td]

45652​

[/td][td]

$ 500.00​

[/td][td] NA [/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]

45698​

[/td][td]

$ 1,500.00​

[/td][td]

45698​

[/td][td]

$ 1,500.00​

[/td][td]

$ -​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td]

46523​

[/td][td]

$ 1,600.00​

[/td][td]

46523​

[/td][td]

$ 1,650.00​

[/td][td]

$ (50.00)​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Payroll Week Ending[/td][/tr][/table]

 

[etaf]

your invoice numbers dont seem to be on the columns you mention A & B - But looks like its B and D - but not sure

=IF ( B2=D2 , C2-D2 , "NA" )

but as i say , I'm not sure - as based on text / picture disagree

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[zone709]

Sounds confusing to me also. Maybe i am explaining wrong. Basically trying to match the invoice numbers with the amount together. A & B verse C & D. If they don't match I made the column E to show me the difference. What's a little more maybe complicated is. I am not trying to match line by line but match going down the whole column. You can see first line returns -150.00 that's easy as its matching the first line, but the second one that show NA. As you can see C & D cant match anything in A & B. Basically trying to match the invoice number and the amount together with A&B to C&D.

 

[KRice]

Try this:

MrExcel_20220826.xlsx
	A	B	C	D	E
1	Invoice Number	Amount	Invoice Number	Amount	Difference
2	25613	$ 600.00	25613	$ 750.00	$ (150.00)
3	15623	$1,200.00	45652	$ 500.00	NA
4	45698	$1,500.00	45698	$1,500.00	$ -
5	46523	$1,600.00	46523	$1,650.00	$ (50.00)
Sheet5

Cell Formulas
Range	Formula
E2:E5	E2	=IFERROR(INDEX($B$2:$B$5,MATCH(C2,$A$2:$A$5,0))-D2,"NA")

 

[zone709]

Krice that it perfect. I change the numbers so it could go down the sheet, but this is what i needed and was hoping I explained it right. Thanks so much.

 

[KRice]

That's great...I'm happy to help. Yes...you will need to adjust the A and B ranges to cover the entire range of interest and "fix" those ranges using the $ signs so that they do not change when the formula is copied down the column.