Excel - how to find a lookup string from a large list
My datasheet consists of a) index numbers and b) rows associated with those index numbers. In my template, the user enters an index number and the string associated with that index number will be displayed.
for example
1 Bag
1 Catsgsg
1 Dogs
1 oygg
2 Stackoverflow
2 tacks
2 Binoculars
2 all
.
.
.
1000 Bonobos
1000 Canopy
1000 Ascot
If the user enters the number 1000, how can I pull out the word bonobos knowing that there is a word b but not the actual word? How can I fix my array formula to work with wildcards?
=INDEX(Partners!$D$2:$D$2227,MATCH(1,(Partners!$A$2:$A$2227=number)*(Partners!$C$2:$C$2227="b *"),0))
EDIT: I was able to customize the callum solution and have a formula for multiple wildcard strings.
=INDEX(Partners!$D$2:$D$2227,MATCH(1,IFERROR(SEARCH(num&"A*",Partners!$A$2:$A$2227&Partners!$D$2:$D$2227),0)+IFERROR(SEARCH(num&"B*",Partners!$A$2:$A$2227&Partners!$D$2:$D$2227),0)+IFERROR(SEARCH(num&"C*",Partners!$A$2:$A$2227&Partners!$D$2:$D$2227),0),0))
source to share
Only certain functions accept wildcards, so you will need to use one of them if you want to use a wildcard *
. SEARCH
is one example:
=INDEX(Partners!$D$2:$D$2227,MATCH(1,IFERROR(SEARCH(number&"b*",Partners!$A$2:$A$2227&Partners!$B$2:$B$2227),0),0))
Or you can just avoid the pattern:
=INDEX(Partners!$D$2:$D$2227,MATCH(number&"b",Partners!$A$2:$A$2227&LEFT(Partners!$B$2:$B$2227,1),0))
both are entered as array formulas ( Ctrl+ Shift+ Enter)
source to share
Try,
=INDEX(B:B, AGGREGATE(15, 6, ROW(A$1:INDEX(A:A, MATCH(1E+99, A:A)))/((A$1:INDEX(A:A, MATCH(1E+99, A:A))=E6)*(LEFT(B$1:INDEX(B:B, MATCH(1E+99, A:A)), 1)=F6)), 1))
In the following sample image shown, I've changed B to C to demonstrate that the formula doesn't just return the first 1000 .
source to share