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?

 

[Aladin Akyurek]

Thanks Your reply I Hope some one give me solution. thanks again

Provided that the problem is understood...

 

[faraz502]

Provided that the problem is understood...

Dear Aladin,

Sorry I cant understand your sentence.

 

[Aladin Akyurek]

Dear Aladin,

Sorry I cant understand your sentence.

I'm sure the problem is obvious to yourself, but I'm less sure it's for the would-be helpers (not for me anyways).

 

[faraz502]

Dear once again i need help

in vba when in past this formula vba give me error, what is the issue in this formula

Range("F2").FormulaR1C1 = "=REPLACE(RC[-1],2,5,"RSL")"

thanks

 

[rchoudha]

Hi,

I have similar kind of scenario, but I could not solve it easily,
My requirement is


Item Value Lookup Count Value
A 23 X 3 34
X 34 89
C 24 82
B 64 A 2 23
X 89 73
X 82
A 73
D 97
C 97

can you please me out in this?

 

[Aladin Akyurek]

Hi,

I have similar kind of scenario, but I could not solve it easily,
My requirement is


Item Value Lookup Count Value
A 23 X 3 34
X 34 89
C 24 82
B 64 A 2 23
X 89 73
X 82
A 73
D 97
C 97

can you please me out in this?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]Item[/td][td]Value[/td][td][/td][td][/td][td]Lookup[/td][td][/td][/tr]

[tr][td]

2​

[/td][td]A[/td][td]

23​

[/td][td][/td][td][/td][td]X[/td][td]A[/td][/tr]

[tr][td]

3​

[/td][td]X[/td][td]

34​

[/td][td][/td][td][/td][td]Count[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]C[/td][td]

24​

[/td][td][/td][td][/td][td]

3​

[/td][td]

2​

[/td][/tr]

[tr][td]

5​

[/td][td]B[/td][td]

64​

[/td][td][/td][td][/td][td]Value[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]X[/td][td]

89​

[/td][td][/td][td][/td][td]

34​

[/td][td]

23​

[/td][/tr]

[tr][td]

7​

[/td][td]X[/td][td]

82​

[/td][td][/td][td][/td][td]

89​

[/td][td]

73​

[/td][/tr]

[tr][td]

8​

[/td][td]A[/td][td]

73​

[/td][td][/td][td][/td][td]

82​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]D[/td][td]

97​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]C[/td][td]

97​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

E4, just enter and copy across to F4:

=COUNTIFS($A$2:$A$10,E$2)

E6, control+shift+enter, not just enter, and copy across to F6, and down:

=IF(ROWS(E$6:E6)<=E$4,INDEX($B$2:$B$10,SMALL(IF($A$2:$A$10=E$2,
    ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(E$6:E6))),"")

 

[Bytor47]

See if this is what you had in mind.

Sheet1

*	A	B	C	D	E	F
*
*
*	*	*
*	*	*
*	*	*
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*
*	*	*	*

<tbody>
[TD="bgcolor: #CACACA, align: center"]1
[/TD]
[TD="align: center"]Item
[/TD]
[TD="align: center"]Value
[/TD]

[TD="align: center"]Lookup
[/TD]
[TD="align: center"]Count
[/TD]
[TD="align: center"]Value
[/TD]

[TD="bgcolor: #CACACA, align: center"]2
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]23
[/TD]

[TD="align: center"]X
[/TD]
[TD="bgcolor: #CCFFCC, align: center"]3
[/TD]
[TD="bgcolor: #CCFFCC, align: center"]34
[/TD]

[TD="bgcolor: #CACACA, align: center"]3
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]34
[/TD]

[TD="bgcolor: #CCFFCC, align: center"]89
[/TD]

[TD="bgcolor: #CACACA, align: center"]4
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]24
[/TD]

[TD="bgcolor: #CCFFCC, align: center"]82
[/TD]

[TD="bgcolor: #CACACA, align: center"]5
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]64
[/TD]

[TD="bgcolor: #CCFFCC"]*
[/TD]

[TD="bgcolor: #CACACA, align: center"]6
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]89
[/TD]

[TD="bgcolor: #CACACA, align: center"]7
[/TD]
[TD="align: center"]X
[/TD]
[TD="align: center"]82
[/TD]

[TD="bgcolor: #CACACA, align: center"]8
[/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]73
[/TD]

[TD="bgcolor: #CACACA, align: center"]9
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]97
[/TD]

[TD="bgcolor: #CACACA, align: center"]10
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]97
[/TD]

</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'm not sure if I should continue with this thread or start a new one but I stumbled across this solution and got it to work but would like to tweak it somewhat.
The data I am looking through is addresses and I would like to allow for partial matches. I have changed one of the formulas and it works fine. The issue is the array formula will do partial matches.

I used this to count the partial matches which works fine:
=COUNTIF(Item,"*"&D2&"*")

What would I need to change in the array formula to handle wildcards? Is this even possible?

I have tried this with no luck:
=IF(ROWS(F$2:F2)>E$2,"",INDEX(Value,SMALL(IF(Item="*"&D$2&"*",ROW(Item)),ROWS(F$2:F2))))

Any assistance is greatly appreciated.

Thank you

 

[Aladin Akyurek]

@Bytor47;4720287]I'm not sure if I should continue with this thread or start a new one but I stumbled across this solution and got it to work but would like to tweak it somewhat.
The data I am looking through is addresses and I would like to allow for partial matches. I have changed one of the formulas and it works fine. The issue is the array formula will do partial matches.

I used this to count the partial matches which works fine:
=COUNTIF(Item,"*"&D2&"*")

What would I need to change in the array formula to handle wildcards? Is this even possible?

I have tried this with no luck:
=IF(ROWS(F$2:F2)>E$2,"",INDEX(Value,SMALL(IF(Item="*"&D$2&"*",ROW(Item)),ROWS(F$2:F2))))

Any assistance is greatly appreciated.

Thank you[/QUOTE]

Control+shift+enter, not just enter, and copy down:

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

Does this work for you?

 

[Bytor47]

@Bytor47;4720287]I'm not sure if I should continue with this thread or start a new one but I stumbled across this solution and got it to work but would like to tweak it somewhat.
The data I am looking through is addresses and I would like to allow for partial matches. I have changed one of the formulas and it works fine. The issue is the array formula will do partial matches.

I used this to count the partial matches which works fine:
=COUNTIF(Item,"*"&D2&"*")

What would I need to change in the array formula to handle wildcards? Is this even possible?

I have tried this with no luck:
=IF(ROWS(F$2:F2)>E$2,"",INDEX(Value,SMALL(IF(Item="*"&D$2&"*",ROW(Item)),ROWS(F$2:F2))))

Any assistance is greatly appreciated.

Thank you

Control+shift+enter, not just enter, and copy down:

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

Does this work for you?[/QUOTE]

Absolutely! It worked perfectly. Thank you so much.

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

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

Does this work for you?

Absolutely! It worked perfectly. Thank you so much.[/QUOTE]

You are welcome.