lookup with Condition of Allowable=Incurred values return a zero for particular Item ref Code.

[mmr1]

Hi,

I required a help in formula where as a item code AI2 (Cell no 4 & 5)does not have any allowable rate , but in that item code has incurred value for Operatives and Equipment (Highlighted Cells).

In this case i required a formula for AI2 should return as a Allowable value = Incurred value, it means there is no allowable value then there is no incurred value both return as zero values.

Thanks for the help in advance,

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1					Allowable Value		Incurred value
2	Item	Measurement	Operative	Equipment	Operative	Equipment	Operative	Equipment			Allowable Rate Per Mtr		Opeartive & Equipment rate per Hr
3	Code	Work Done in mtr	Worked Hrs		Estimated		Consumed			Item Code	Operative	Equipment	Item Code	Operative	Equipment
4	AI1	10	25	10	120	450	300	120		AI1	12	45	AI1	12	45
5	AI2	50	30	5	0	0	420	70		AI2			AI2	14	14
6	AI2	15	47	60	0	0	658	840		AI2			AI2	15	15
7	AI4	39	26	11	546	585	364	154		AI4	14	15	AI4	14	15
Sheet1

Cell Formulas
Range	Formula
E4:E7	E4	=IFERROR(VLOOKUP($A4,$J$4:$L$9,2,FALSE),0)*B4
F4:F7	F4	=IFERROR(VLOOKUP($A4,$J$4:$L$9,3,FALSE),0)*B4
G4:H7	G4	=+IFERROR(VLOOKUP($A4,$M$4:$O$9,2,FALSE),0)*C4

 

[AlanY]

will this work for you?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1					Allowable Value		Incurred value
2	Item	Measurement	Operative	Equipment	Operative	Equipment	Operative	Equipment			Allowable Rate Per Mtr		Opeartive & Equipment rate per Hr
3	Code	Work Done in mtr	Worked Hrs		Estimated		Consumed			Item Code	Operative	Equipment	Item Code	Operative	Equipment
4	AI1	10	25	10	120	450	300	120		AI1	12	45	AI1	12	45
5	AI2	50	30	5	420	70	420	70		AI2			AI2	14	14
6	AI2	15	47	60	658	840	658	840		AI2			AI2	15	15
7	AI4	39	26	11	546	585	364	154		AI4	14	15	AI4	14	15
8
Sheet3

Cell Formulas
Range	Formula
E4:E7	E4	=IF(IFERROR(VLOOKUP($A4,$J$4:$L$9,2,FALSE),0)*$B4=0,G4,IFERROR(VLOOKUP($A4,$J$4:$L$9,2,FALSE),0)*$B4)
F4:F7	F4	=IF(IFERROR(VLOOKUP($A4,$J$4:$L$9,3,FALSE),0)*$B4=0,H4,IFERROR(VLOOKUP($A4,$J$4:$L$9,3,FALSE),0)*$B4)
G4:H7	G4	=+IFERROR(VLOOKUP($A4,$M$4:$O$9,2,FALSE),0)*C4

 

[mmr1]

Thanks Alan,

For your valuable response, actually i am look it for allowable value=Incurred value (E5:F5=G5:F5) to be zero in this case, it means whenever the item code AI2 repeated at that time allowable value and incurred value should be zero,because of there is no allowable value against the item code.or else included in other item code the against incurred cost also included as it is.



Thanks,

 

[AlanY]

i'm a bit lost, do you mean this?

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1					Allowable Value		Incurred value
2	Item	Measurement	Operative	Equipment	Operative	Equipment	Operative	Equipment			Allowable Rate Per Mtr		Opeartive & Equipment rate per Hr
3	Code	Work Done in mtr	Worked Hrs		Estimated		Consumed			Item Code	Operative	Equipment	Item Code	Operative	Equipment
4	AI1	10	25	10	120	450	300	120		AI1	12	45	AI1	12	45
5	AI2	50	30	5	0	0	0	0		AI2			AI2	14	14
6	AI2	15	47	60	0	0	0	0		AI2			AI2	15	15
7	AI4	39	26	11	546	585	364	154		AI4	14	15	AI4	14	15
8
Sheet5

Cell Formulas
Range	Formula
E4:E7	E4	=IFERROR(VLOOKUP($A4,$J$4:$L$9,2,FALSE),0)*B4
F4:F7	F4	=IFERROR(VLOOKUP($A4,$J$4:$L$9,3,FALSE),0)*B4
G4:H7	G4	=IF(E4=0,0,IFERROR(VLOOKUP($A4,$M$4:$O$9,2,FALSE),0)*C4)

 

[mmr1]

Thanks for the response,

at present scenario suggested formula working perfectly, but i have a some codes which are does not have allowable values but incurred values to be reflected as it is.

for example in that condition E5:F5 =Did not return any values, G5:H5=Return the values whatever cost incurred.


Thanks,

 

[AlanY]

Thanks for the response,

at present scenario suggested formula working perfectly, but i have a some codes which are does not have allowable values but incurred values to be reflected as it is.

for example in that condition E5:F5 =Did not return any values, G5:H5=Return the values whatever cost incurred.


Thanks,

it would be helpful if you could post a sample data with all the scenario and desired results

 

[mmr1]

Here the scenario is whenever AI2 code we Put in any cell header Item Code formula to be return zero whether it is Allowable Value or it its Incurred value,

like that we put AI5 Code in any cell header Item Code formula to be return zero in Allowable value but incurred Value to return as actual in cell G8 and H8.

Thanks,

Lookup condition Allowable= Incurred values.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1					Allowable Value		Incurred value
2	Item	Measurement	Operative	Equipment	Operative	Equipment	Operative	Equipment			Allowable Rate Per Mtr		Opeartive & Equipment rate per Hr
3	Code	Work Done in mtr	Worked Hrs		Estimated		Consumed			Item Code	Operative	Equipment	Item Code	Operative	Equipment
4	AI1	10	25	10	120	450	300	120		AI1	12	45	AI1	12	45
5	AI2	50	30	5	0	0	0	0		AI2			AI2	14	14
6	AI2	15	47	60	0	0	0	0		AI2			AI2	15	15
7	AI4	39	26	11	546	585	364	154		AI4	14	15	AI4	14	15
8	AI5	22	11	10	0	0	0	0		AI5			AI5	21	41
Sheet3

Cell Formulas
Range	Formula
E4:E8	E4	=IFERROR(VLOOKUP($A4,$J$4:$L$9,2,FALSE),0)*B4
F4:F8	F4	=IFERROR(VLOOKUP($A4,$J$4:$L$9,3,FALSE),0)*B4
G4:H8	G4	=IF(E4=0,0,IFERROR(VLOOKUP($A4,$M$4:$O$9,2,FALSE),0)*C4)

 

[AlanY]

ok, based on post #7 above, what do you expected to see in E8:H8, and E5:H5 just to help to understand the requirement

 

[mmr1]

1) E5:H5 return as Zero,

2)E8:F8 return as Zero,

3)G8:H8 return a value as (11*21=231 in Cell G8) & (10*41=410 in Cell H8) showed values to be come dragging the same formula.

 

[AlanY]

i'm confused.

Why there is difference between G5:H5 (Al2, =0) and G8:H8 (Al5, 231 and 410)?