Vlookup --> find next

Sunvisor said:

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?

Click to expand...

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

T. Valko said:

See if this is what you had in mind.

Book1
*	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	*	*	*	*
Sheet1

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.

Click to expand...

T. Valko said:

See if this is what you had in mind.

Book1
*	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	*	*	*	*
Sheet1

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.

Click to expand...

herpasymplex10 said:

How would I go about tweaking this formula?

Trying to help a co-worker

Spreadsheet looks something like this:
A B C
Item Component # of Comp
25051 x 2
25051 x 2
25051 x 2




the formula would go in column B where the "x" are

Column A goes on for a long time (1500 rows)
the formula would be similiar to the above post.
The only problem is, I don't know how to 'reset' the formula
when the item number in column A changes.

It works great for the top most part number when you copy it down,
but when it encounters the next part number in Column A, it puts in blanks

Help?

Click to expand...

Alex Barrosse said:

I had an additional question about the formulas you posted:
=COUNTIF(Item,D2) and
=IF(ROWS(F$2:F2)>E$2,"",INDEX(Value,SMALL(IF(Item=D$2,ROW(Item)),ROWS(F$2:F2))))

I ran them on a similar data set to the one you posted but the problem is, my "Item" column has codes that are of varying lengths and contain numbers and letters (example: H35). The issue is that the formula returns values for all the values in the "Item" column that start with whatever letter the code in D$2 starts with. So I'm getting values from rows that contain H35, H25, HC5, HXF, etc, when I only want H35.

My question is basically, is there a way to tweak the formula so it only returns exact matches, rather than partial matches?

Thanks for any ideas.

Click to expand...