IFs & VLOOKUP Help

[Gbadd]

I am trying to calculate the number of cases to pick for deliveries. I have a table that states how many each (base unit) is in a case. I need vlookup to find the amount in the table array and divide that value by the quantity listed in column D (base unit quantity). That's a simple vlookup but sometimes my report already has the number of cases that is needed to pick so the vlookup gives me an incorrect value. I tried to combine a IFs and vlookup but getting a #VALUE! error. My formula is =IFS(E3="CS",D3),D3/VLOOKUP(F3,base,10,FALSE) Maybe IFs is not what I need to use. Can someone help?

 

[etaf]

=IFS(E3="CS",D3),D3/VLOOKUP(F3,base,10,FALSE)

i cannot follow the image to well,

SO lets do it with an IF , as IFS has to evaluate all conditions - so i tend ti avoid that

If E3 = CS , then you want to display the value thats on D3
if E3 does not = CS then you want to divide D3 by a lookup value, which looks up the value in F3 in a range named base and brings back the 10th column from the starting reference column in the name base

=IF( E3="CS", D3, D3/VLOOKUP(F3,base,10,FALSE) )

BUT you will get an error if the vlookup cannot find F3 value in the first column of the range name 'BASE'
so you could use na IFERROR

Or if the value returned is a 0 or text it will also error -

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

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

Make sure you set any share or google to share to everyone

 

[TheRedCardinal]

Do you need it to be IFs? which is usually used for multiple conditions?

What about:

=IF (E3 = "CS", D3, D3/Vlookup(F3, Base, 10,False)

Note I've not checked your formula itself, but have just taken it to be correct and slotted it into a simple IF statement.

 

[Gbadd]

Ok so honestly I do not care what type of formula is used I just what results. Basically I need to convert the quantity in column D to case quantity if not already displayed as CS (cases). If column E is not "CS" I want excel to find the material in the table and take the base/case quantity (column Q) and divide it by delivery quantity (column D). If the value in column E is "CS" enter the value in column D into column J. I have made the values in column J to match what I want the end result to be. Can you supply a formula to get these results?

List For Picking.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2													Table
3	Delivery	Item	Delivery quantity	SU	Material	Plnt	SLoc	Description	Value I need returned
4	87841369	10	12	EA	12105PTM	D796	STCK	6x5x1 LB SHOCK PLUS	2				Material	Description	Base UoM	Base/Case
5	87841369	20	4	EA	15128PTM	D796	STCK	4x1 GA ALG/CLRFR	1				12105PTM	6x5x1 LB SHOCK PLUS	BOX	6
6	87841369	30	6	EA	15145PTM	D796	STCK	6x1 QT ALG MAXBLUE	1				15128PTM	4x1 GA ALG/CLRFR	BT	4
7	87841369	40	6	EA	17456STM	D796	STCK	6x14 OZ BROM GRAN	1				15145PTM	6x1 QT ALG MAXBLUE	BT	6
8	87850251	10	3	CS	42610BIO	D796	STCK	4x4.5 LB SPA GRD BROM TAB	3				17456STM	6x14 OZ BROM GRAN	BT	6
9	87850251	20	3	CS	42640BIO	D796	STCK	4x70 OZ SPA GRD SPA COMPLETE	3				25768BIO	6x2 LT POOL COMPLETE 911	BT	6
10	87850251	30	4	CS	25768BIO	D796	STCK	6x2 LT POOL COMPLETE 911	4				42610BIO	4x4.5 LB SPA GRD BROM TAB	BT	4
11													42640BIO	4x70 OZ SPA GRD SPA COMPLETE	BT	4
List For Picking

 

[etaf]

Ok so honestly I do not care what type of formula is used I just what results.

did you try the formula

you have changed the example so now the vlookup is different from your orginal example

=IF( E3="CS", D3, D3/VLOOKUP(F3,$N$3:$Q$10,4,FALSE) )

so this works , with the 2nd example you gave - if the real data uses base as the range name and column 10 from the reference , then you will need to adapt

lookup and divide - ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Table
2		Delivery	Item	Delivery quantity	SU	Material	Plnt	SLoc	Description	Value I need returned	Formula
3		87841369	10	12	EA	12105PTM	D796	STCK	6x5x1 LB SHOCK PLUS	2	2			Material	Description	Base UoM	Base/Case
4		87841369	20	4	EA	15128PTM	D796	STCK	4x1 GA ALG/CLRFR	1	1			12105PTM	6x5x1 LB SHOCK PLUS	BOX	6
5		87841369	30	6	EA	15145PTM	D796	STCK	6x1 QT ALG MAXBLUE	1	1			15128PTM	4x1 GA ALG/CLRFR	BT	4
6		87841369	40	6	EA	17456STM	D796	STCK	6x14 OZ BROM GRAN	1	1			15145PTM	6x1 QT ALG MAXBLUE	BT	6
7		87850251	10	3	CS	42610BIO	D796	STCK	4x4.5 LB SPA GRD BROM TAB	3	3			17456STM	6x14 OZ BROM GRAN	BT	6
8		87850251	20	3	CS	42640BIO	D796	STCK	4x70 OZ SPA GRD SPA COMPLETE	3	3			25768BIO	6x2 LT POOL COMPLETE 911	BT	6
9		87850251	30	4	CS	25768BIO	D796	STCK	6x2 LT POOL COMPLETE 911	4	4			42610BIO	4x4.5 LB SPA GRD BROM TAB	BT	4
10														42640BIO	4x70 OZ SPA GRD SPA COMPLETE	BT	4
11
12
13
Sheet4

Cell Formulas
Range	Formula
K3:K9	K3	=IF( E3="CS", D3, D3/VLOOKUP(F3,$N$3:$Q$10,4,FALSE) )

 

[Gbadd]

Thanks for the quick response! Everything works now. Thank you

 

[etaf]

you are welcome