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))

      

+3


source to share


2 answers


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)

+4


source


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 .

enter image description here

+2


source







All Articles