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?

 

[Akashwani]

Hi,

Is this the scenario you have?.....

http://www.mrexcel.com/forum/showthread.php?t=545679

Ak

 

[T. Valko]

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?

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.

 

[aalokwar]

No Dear,
I want to compare each cell with and return if matching value found.
See I have attached file in another forum as here attachment is not allowed.

http://www.excelforum.com/excel-gen...value-using-vlookup-function.html#post2527039

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.

 

[herpasymplex10]

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.

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?

 

[T. Valko]

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?

Not sure I understand what you're asking.

Maybe all you need to do is change this portion:

...ROWS(...)...

To something like this:

...COUNTIF(A$2:A2,A2)...

 

[aalokwar]

Thanks Dear,
I got formula (see below) for the question and worked perfectly.

=IF(ISNA(VLOOKUP($A11,A$2:$C$6,1,FALSE)),"NF",IF(SUMPRODUCT(($A$2:$A$6=$A11)*($B$2:$B$6="Red"))>0,"Y","N"))

 

[Alex Barrosse]

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.

 

[T. Valko]

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.

Can you post some sample data?

 

[SirMille]

Anyway to make this arrayformula show found results in the column beside, instead of new rows?