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

 

[mmr1]

The case is AI2 has No allowance=No Cost because of this item code values covered in another Item code for record purpose only just recorded measurements and Hours only.

The Case AI5 there is no allowance =against the incurred Cost,That is why item code AI5 Cost to be taken as it is whatever incurred.


Thanks,

 

[AlanY]

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)

sorry, just can't see the difference from the data in Rows5 & 8.
i'm logging off now, hopefully tomorrow with a clear mind I can spot the difference

 

[mmr1]

great full to you for your help looking forward towards solution.

However i utilize for now your suggested formula, please let me know if get any solution,thanks again.

 

[AlanY]

sorry, just can't see the difference from the data in Rows5 & 8.
i'm logging off now, hopefully tomorrow with a clear mind I can spot the difference

unless you cad clearly explain the difference requirement between Al2 & Al5 I'm afraid not much I can do here