Vlookup --> find next

[Sunvisor]

Lets say you are using a vlookup to a value but there are multiple values with different results...

Is there a formula to do a vlookup and then in the row underneath say if vlookup = the above result, go to next?

 

[Snupple]

See if this is what you had in mind.

Sheet1

*	A	B	C	D	E	F
1	Item	Value	*	Lookup	Count	Value
2	A	23	*	X	3	34
3	X	34	*	*	*	89
4	C	24	*	*	*	82
5	B	64	*	*	*	*
6	X	89	*	*	*	*
7	X	82	*	*	*	*
8	A	73	*	*	*	*
9	D	97	*	*	*	*
10	C	97	*	*	*	*

<tbody>
</tbody>

You want to lookup all instances of X and return the corresponding values.

In the formulas I use the following defined named ranges:

• Item
• Refers to: =Sheet1!$A$2:$A$10
• Value
• Refers to: =Sheet1!$B:$B

Enter this formula in E2. This will return the count of records for the lookup value.

=COUNTIF(Item,D2)

Enter this array formula** in F2. This will extract the corresponding data for the lookup value.

=IF(ROWS(F$2:F2)>E$2,"",INDEX(Value,SMALL(IF(Item=D$2,ROW(Item)),ROWS(F$2:F2))))

Copy down until you get blanks.

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

I am currently using the above formula and it works great, but I need a bit of a hand to extend it:

What if column C now has additional Lookup values, so you want the results with X in column A AND E in Column C?

Sheet1

*	A	B	C	D	E	F
1	Item	Value	*	Lookup	Count	Value
2	A	23	*	X AND E	2	34
3	X	34	E	*	*	89
4	C	24	*	*	*	*
5	B	64	*	*	*	*
6	X	89	E	*	*	*
7	X	82	*	*	*	*
8	A	73	*	*	*	*
9	D	97	*	*	*	*
10	C	97	*	*	*	*

<tbody>
</tbody>

If that possible ?

Cheers - Rick

 

[Aladin Akyurek]

@Snupple

Row\Col	A​	B​	C​	D​	E​	F​
1​	Item	Value		Lookup		Count
2​	A	23​		X	E	2​
3​	X	34​	E			Value
4​	C	24​				34​
5​	B	64​				34​
6​	X	89​	E			34​
7​	X	82​				34​
8​	A	73​
9​	D	97​
10​	C	97​

In F2 just enter:

=COUNTIFS(A2:A10,D2,C2:C10,E2)

In F4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F4:F4)>$F$2,"",INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,IF($C$2:$C$10=$E$2,ROW($B$2:$B$10)-ROW($B$2)+1)),ROWS($F4:F4))))

 

[Snupple]

@Snupple

Row\Col	A​	B​	C​	D​	E​	F​
1​	Item	Value		Lookup		Count
2​	A	23​		X	E	2​
3​	X	34​	E			Value
4​	C	24​				34​
5​	B	64​				34​
6​	X	89​	E			34​
7​	X	82​				34​
8​	A	73​
9​	D	97​
10​	C	97​

<tbody>
</tbody>

In F2 just enter:

=COUNTIFS(A2:A10,D2,C2:C10,E2)

In F4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F4:F4)>$F$2,"",INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,IF($C$2:$C$10=$E$2,ROW($B$2:$B$10)-ROW($B$2)+1)),ROWS($F4:F4))))

Thanks for the help .. Unfortunately the resulted "Value" In column F are not correct though.. There should be only 2 values, 34 & 89 as those are the only 2 that have X in column A and E in column C ?

 

[Aladin Akyurek]

Thanks for the help .. Unfortunately the resulted "Value" In column F are not correct though.. There should be only 2 values, 34 & 89 as those are the only 2 that have X in column A and E in column C ?

My bad. ROWS($F4:F4) should have been ROWS($F$4:F4)...

To re-cap:

Row\Col	A​	B​	C​	D​	E​	F​
1​	Item	Value		Lookup		Count
2​	A	23​		X	E	2​
3​	X	34​	E			Value
4​	C	24​				34​
5​	B	64​				89​
6​	X	89​	E
7​	X	82​
8​	A	73​
9​	D	97​
10​	C	97​

In F2 just enter:

=COUNTIFS(A2:A10,D2,C2:C10,E2)

This return a count of records that meet the conditions.

In F4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$4:F4)>$F$2,"",INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,IF($C$2:$C$10=$E$2,ROW($B$2:$B$10)-ROW($B$2)+1)),ROWS($F$4:F4))))

This lists the records that meet the conditions.

 

[Snupple]

My bad. ROWS($F4:F4) should have been ROWS($F$4:F4)...

To re-cap:

Row\Col	A​	B​	C​	D​	E​	F​
1​	Item	Value		Lookup		Count
2​	A	23​		X	E	2​
3​	X	34​	E			Value
4​	C	24​				34​
5​	B	64​				89​
6​	X	89​	E
7​	X	82​
8​	A	73​
9​	D	97​
10​	C	97​

<tbody>
</tbody>

In F2 just enter:

=COUNTIFS(A2:A10,D2,C2:C10,E2)

This return a count of records that meet the conditions.

In F4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$4:F4)>$F$2,"",INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,IF($C$2:$C$10=$E$2,ROW($B$2:$B$10)-ROW($B$2)+1)),ROWS($F$4:F4))))

This lists the records that meet the conditions.

You the man, thank you so much!

 

[Aladin Akyurek]

You the man, thank you so much!

Glad to help.