Thanks Your reply I Hope some one give me solution. thanks again
Provided that the problem is understood...
Dear Aladin,
Sorry I cant understand your sentence.
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?
Row\Col | A | B | C | D | E | F |
1 | Item | Value | Lookup | |||
2 | A | 23 | X | A | ||
3 | X | 34 | Count | |||
4 | C | 24 | 3 | 2 | ||
5 | B | 64 | Value | |||
6 | X | 89 | 34 | 23 | ||
7 | X | 82 | 89 | 73 | ||
8 | A | 73 | 82 | |||
9 | D | 97 | ||||
10 | C | 97 |
=COUNTIFS($A$2:$A$10,E$2)
=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))),"")
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:
Enter this formula in E2. This will return the count of records for the lookup value.
- Item
- Refers to: =Sheet1!$A$2:$A$10
- Value
- Refers to: =Sheet1!$B:$B
=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.
@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?