How to calculate data in a cell from two possible columns (A or B)

[mediumrare]

Rebates
	A	B	C	D	E	F	G	H	I	J
1	product	EA cost	CS cost	vendor ref	EA/CS		product report	qty	uom	ext
2	soap1	$ 11.00	$ 44.00	$ 44.00	4		soap1	1	CS
3	soap2	$ 22.00	$ 88.00	$ 90.00	4		soap1	11	EA
4	soap4	$ 15.00	$ 30.00	$ 30.00	2		soap4	5	CS
5	soap27	$ 2.50	$ 30.00	$ 30.00	12		soap2	7	CS
6							soap1	22	CS
7							soap27	2	CS
8							soap4	1,200	EA
Sheet4

I'm not sure if I'm describing this correctly/succinctly in the thread title.

I'm trying to have it search a cell to see if it says "EA" or "CS," then pull either the EA $ column or the CS $ column data from the same row, and multiply that by the quantity ordered into extension column of the "product report" table side.

Along the same lines (I think?), I'd like to have the "vendor ref" data be pulled from another sheet in the same workbook. It could search for the product number ("product"), if it matches, then it returns the price in, says, column B.


Honestly, it takes up so much of my time doing this rebate report every month, I wish I could learn everything I'd want to do or, if not that, I'd legitimately pay someone to help me put together what I'd dream of it doing. Because, right now, it's manual. Looking at this, referencing, verifying, compiling, multiplying, matching a contract number to a customer, etc.

 

[kweaver]

Need more information to handle the vendor ref.
Otherwise, is this what you mean for the "ext"? Maybe this will give you a way to calculate the "vendor ref" ?

Book1
	A	B	C	D	E	F	G	H	I	J
1	Product	EA Cost	CS Cost	`Vendor Ref	EA/CS		Report	QTY	UOM	Ext
2	soap1	$ 11.00	$ 44.00	??	4		soap1	1	CS	$ 44.00
3	soap2	$ 22.00	$ 88.00	??	4		soap1	11	EA	$ 121.00
4	soap4	$ 15.00	$ 30.00	??	2		soap4	5	CS	$ 150.00
5	soap27	$ 2.50	$ 30.00	??	12		soap2	7	CS	$ 616.00
6							soap1	22	CS	$ 968.00
7							soap27	2	CS	$ 60.00
8							soap4	1,200	EA	$ 18,000.00
Report

Cell Formulas
Range	Formula
J2:J8	J2	=H2*INDEX($B$2:$C$5,MATCH(G2,$A$2:$A$5,0),MATCH(I2,LEFT($B$1:$C$1,2),0))

 

[mediumrare]

Need more information to handle the vendor ref.
Otherwise, is this what you mean for the "ext"? Maybe this will give you a way to calculate the "vendor ref" ?

Book1
	A	B	C	D	E	F	G	H	I	J
1	Product	EA Cost	CS Cost	`Vendor Ref	EA/CS		Report	QTY	UOM	Ext
2	soap1	$ 11.00	$ 44.00	??	4		soap1	1	CS	$ 44.00
3	soap2	$ 22.00	$ 88.00	??	4		soap1	11	EA	$ 121.00
4	soap4	$ 15.00	$ 30.00	??	2		soap4	5	CS	$ 150.00
5	soap27	$ 2.50	$ 30.00	??	12		soap2	7	CS	$ 616.00
6							soap1	22	CS	$ 968.00
7							soap27	2	CS	$ 60.00
8							soap4	1,200	EA	$ 18,000.00
Report

Cell Formulas
Range	Formula
J2:J8	J2	=H2*INDEX($B$2:$C$5,MATCH(G2,$A$2:$A$5,0),MATCH(I2,LEFT($B$1:$C$1,2),0))

Thanks, very much. Yes, that was what I meant. The "ext" (extension) as the correct pricing (each or case) times the quantity ordered.

Are you using LEFT so that it knows where to start looking? Or what else?

 

[mediumrare]

Need more information to handle the vendor ref.
Otherwise, is this what you mean for the "ext"? Maybe this will give you a way to calculate the "vendor ref" ?

The "vendor ref" goal(s):
- pulling the vendor pricing from another sheet when the product numbers match; and
- conditionally formatting the "vendor ref" to compare pricing discrepancies with the case price listed

What happens now for these monthly reports:
1. open report for the last month's purchases for Vendor A
2. open rebate template
3. open reference workbook (vendor pricing, contract info [customer, contract no., contract pricing], our into-stock cost reports)
4. rearrange the column order from the report and copy it into the rebate template
5. copy the products into a sheet in the reference wb that has a comparative list of our product numbers next to the vendor's numbers
6. runs an IFERROR(VLOOKUP...) to match the product number from the report with the correct vendor number
7. manually compare products copied into rebate template with products on active contracts, removing ineligible products
8. cross-check UOM (ea/cs) with pricing to request the correct rebate amount
9. manually pull the contract info and match it to the customer
10. die a little inside

If there are any number of steps I can take to mitigate errors, better manage my time, etc., then I'm in.

 

[kweaver]

Thanks, very much. Yes, that was what I meant. The "ext" (extension) as the correct pricing (each or case) times the quantity ordered.

Are you using LEFT so that it knows where to start looking? Or what else?

I used "LEFT" because the column headers in B and C (EA Cost and CS Cost) had the word "Cost" in the heading, so an = would not find it matches.

 

[kweaver]

The "vendor ref" goal(s):
- pulling the vendor pricing from another sheet when the product numbers match; and
- conditionally formatting the "vendor ref" to compare pricing discrepancies with the case price listed

What happens now for these monthly reports:
1. open report for the last month's purchases for Vendor A
2. open rebate template
3. open reference workbook (vendor pricing, contract info [customer, contract no., contract pricing], our into-stock cost reports)
4. rearrange the column order from the report and copy it into the rebate template
5. copy the products into a sheet in the reference wb that has a comparative list of our product numbers next to the vendor's numbers
6. runs an IFERROR(VLOOKUP...) to match the product number from the report with the correct vendor number
7. manually compare products copied into rebate template with products on active contracts, removing ineligible products
8. cross-check UOM (ea/cs) with pricing to request the correct rebate amount
9. manually pull the contract info and match it to the customer
10. die a little inside

If there are any number of steps I can take to mitigate errors, better manage my time, etc., then I'm in.

This is quite a bit of into to digest without an example.

 

[mediumrare]

I used "LEFT" because the column headers in B and C (EA Cost and CS Cost) had the word "Cost" in the heading, so an = would not find it matches.

Ah, gotcha. So searching the first 2 characters from the left.

 

[mediumrare]

This is quite a bit of into to digest without an example.

I'll see if I can't de-identify the data to make it shareable publicly.