VLOOKUP That Ignores 0 values

[Browneh89]

Hello,

I am attempting to find a lookup formula which ignores a value in a table and returns to me the value I require.

Below is an example of what I'm attempting to do;

Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 1	Result Im Getting			Table 2					Table 3
2	Lane	REQ	REQ2		Lane	ID	REQ1	REQ2		ID	REQ	REQ2
3	10	STORE	0		10	114	STORE	0		114	STORE
4	12	0	0		10	114	STORE	0		224		HOLD
5	14	0	HOLD		10	200	0	0
6	16	STORE	0		12	200	0	0
7	18	0	0		12	114	STORE	0
8					14	224	0	HOLD
9					14	224	0	HOLD
10	Table 1	Result I Want			14	300	0	0
11	Lane	REQ	REQ2		16	114	STORE	0
12	10	STORE	0		18	300	0	0
13	12	STORE	0		18	300	0	0
14	14	0	HOLD		18	224	0	HOLD
15	16	STORE	0
16	18	0	HOLD
17
Sheet1

Specific ID's have Requirements and those ID's are attached to a lane.

In table 1 I want the Lane to show the Requirement however because multiple ID's in Table 2 show up which cause the lookup to fail in table 1.

Is the a simple formula which will allow the lookup to ignore the 0's and just return the Requirement value from table 2.

Any help would be appreciated!

 

[Sockertease]

Hey, you can use index to ignore those rows with zero entry, in your sheet above try using the formula:

=IF(COUNTIFS(H$4:H$15,"<>0",$F$4:$F$15,$B13)=0,0,INDEX(H$4:H$15,MATCH(1,((H$4:H$15<>0)*($F$4:$F$15=$B13)),0)))

COUNTIFS(H$4:H$15,"<>0",$F$4:$F$15,$B13) - Checking if you have no non-zero entries for a given lane, if so return 0

INDEX(H$4:H$15,MATCH(1,((H$4:H$15<>0)*($F$4:$F$15=$B13)),0))) - Finding the first instance of a non-zero entry that also matches the lane number you're looking for

this works because within an index function in excel will evaluate what you pass in as vectors, so the match witin the index function will result in a single row number, the first non=zero entry ((H$4:H$15<>0)) where lane is equal to the lane number in your row (($F$4:$F$15=$B13))

 

[Browneh89]

Hey, you can use index to ignore those rows with zero entry, in your sheet above try using the formula:

=IF(COUNTIFS(H$4:H$15,"<>0",$F$4:$F$15,$B13)=0,0,INDEX(H$4:H$15,MATCH(1,((H$4:H$15<>0)*($F$4:$F$15=$B13)),0)))

COUNTIFS(H$4:H$15,"<>0",$F$4:$F$15,$B13) - Checking if you have no non-zero entries for a given lane, if so return 0

INDEX(H$4:H$15,MATCH(1,((H$4:H$15<>0)*($F$4:$F$15=$B13)),0))) - Finding the first instance of a non-zero entry that also matches the lane number you're looking for

this works because within an index function in excel will evaluate what you pass in as vectors, so the match witin the index function will result in a single row number, the first non=zero entry ((H$4:H$15<>0)) where lane is equal to the lane number in your row (($F$4:$F$15=$B13))

Hey,

Sorry for the late response I have been really busy.

Thanks for your help. the are parts of this formula I do not understand.

=IF(COUNTIFS(H$4:H$15,"<>0",$F$4:$F$15,$B13)=0,0,INDEX(H$4:H$15,MATCH(1,((H$4:H$15<>0)*($F$4:$F$15=$B13)),0)))

Why does the formula reference B13 the Store restriction when it's looking up in the column for the HOLD restriction. Could I not replace =$B13 with ="STORE" or ="HOLD"?

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 1				Table 2					Table 3
2	Lane	REQ	REQ2		Lane	ID	REQ1	REQ2		ID	REQ	REQ2
3	10	STORE	0		10	114	STORE	0		114	STORE
4	12	STORE	0		10	114	STORE	0		224		HOLD
5	14	0	HOLD		10	200	0	0
6	16	STORE	0		12	200	0	0
7	18	0	HOLD		12	114	STORE	0
8					14	224	0	HOLD
9					14	224	0	HOLD
10					14	300	0	0
11					16	114	STORE	0
12					18	300	0	0
13					18	300	0	0
14					18	224	0	HOLD
Summary

Cell Formulas
Range	Formula
B3:C7	B3	=INDEX(FILTER(G$3:G$14,($E$3:$E$14=$A3)*(G$3:G$14<>0),0),1)
G3:G14	G3	=IFNA(VLOOKUP(F3,J:K,2,FALSE),0)
H3:H14	H3	=IFNA(VLOOKUP(F3,J:L,3,FALSE),0)

 

[Browneh89]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 1				Table 2					Table 3
2	Lane	REQ	REQ2		Lane	ID	REQ1	REQ2		ID	REQ	REQ2
3	10	STORE	0		10	114	STORE	0		114	STORE
4	12	STORE	0		10	114	STORE	0		224		HOLD
5	14	0	HOLD		10	200	0	0
6	16	STORE	0		12	200	0	0
7	18	0	HOLD		12	114	STORE	0
8					14	224	0	HOLD
9					14	224	0	HOLD
10					14	300	0	0
11					16	114	STORE	0
12					18	300	0	0
13					18	300	0	0
14					18	224	0	HOLD
Summary

Cell Formulas
Range	Formula
B3:C7	B3	=INDEX(FILTER(G$3:G$14,($E$3:$E$14=$A3)*(G$3:G$14<>0),0),1)
G3:G14	G3	=IFNA(VLOOKUP(F3,J:K,2,FALSE),0)
H3:H14	H3	=IFNA(VLOOKUP(F3,J:L,3,FALSE),0)

Unfortunately the version of 365 that i use at work does not have the filter function available

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 1				Table 2					Table 3
2	Lane	REQ	REQ2		Lane	ID	REQ1	REQ2		ID	REQ	REQ2
3	10	STORE	0		10	114	STORE	0		114	STORE
4	12	STORE	0		10	114	STORE	0		224		HOLD
5	14	0	HOLD		10	200	0	0
6	16	STORE	0		12	200	0	0
7	18	0	HOLD		12	114	STORE	0
8					14	224	0	HOLD
9					14	224	0	HOLD
10					14	300	0	0
11					16	114	STORE	0
12					18	300	0	0
13					18	300	0	0
14					18	224	0	HOLD
15
Summary

Cell Formulas
Range	Formula
B3:B7	B3	=IF(COUNTIFS(G:G,"Store",E:E,A3),"STORE",0)
C3:C7	C3	=IF(COUNTIFS(H:H,"Hold",E:E,A3),"HOLD",0)
G3:G14	G3	=IFNA(VLOOKUP(F3,J:K,2,FALSE),0)
H3:H14	H3	=IFNA(VLOOKUP(F3,J:L,3,FALSE),0)

 

[Browneh89]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 1				Table 2					Table 3
2	Lane	REQ	REQ2		Lane	ID	REQ1	REQ2		ID	REQ	REQ2
3	10	STORE	0		10	114	STORE	0		114	STORE
4	12	STORE	0		10	114	STORE	0		224		HOLD
5	14	0	HOLD		10	200	0	0
6	16	STORE	0		12	200	0	0
7	18	0	HOLD		12	114	STORE	0
8					14	224	0	HOLD
9					14	224	0	HOLD
10					14	300	0	0
11					16	114	STORE	0
12					18	300	0	0
13					18	300	0	0
14					18	224	0	HOLD
15
Summary

Cell Formulas
Range	Formula
B3:B7	B3	=IF(COUNTIFS(G:G,"Store",E:E,A3),"STORE",0)
C3:C7	C3	=IF(COUNTIFS(H:H,"Hold",E:E,A3),"HOLD",0)
G3:G14	G3	=IFNA(VLOOKUP(F3,J:K,2,FALSE),0)
H3:H14	H3	=IFNA(VLOOKUP(F3,J:L,3,FALSE),0)

Haha Fluffy you seem to solve all my problems.

Thanks very much! This solved the problem easily. I didn't even come close to thinking about using a Countif to solve this.

 

[Fluff]

Glad we could help & thanks for the feedback.