Match w/more than one result

G

greasybob

I'm using the Index & Match function to show text in column B that is i
the same row as my searchword in column A.
Formula is : =index(b1:b200,match("John",a1:b200,false),1)

This will find a match for "John" in column A and show what is to th
right in column B.

The problem is that "John" appears more than once in column A and
need a formula that shows all matches instead of just the first.

Any help is appreciated
 
J

JE McGimpsey

for showing all matches, you're probably better off using
Data/AutoFilter.
 
R

Ragdyer

AutoFilter is the quickest and easiest way to go.

A formula would return a single result, so you would need an array of
formulas to return an *unknown quantity* of results (just how many Johns are
there?).

But if you really need a formula, you could try this:

Labels in Row 1

Name to look up in C1

Data in A2:B100

Enter this formula in C2:

=INDEX(B2:B100,SMALL(IF(A2:A100=C1,ROW(A2:A100)-1,""),ROW(A2:A100)-1))

Now, you have to approximate how many times the name will appear in
Column A, and then add extra rows of this formula, in order to insure that
all occurrences are returned.

Say that you could have approximately 10 occurrences.
Then drag and copy this formula down for 15 rows, hit F2, and then do
CSE (<Ctrl> <Shift> <Enter>) to make this an array formula.

If it's done correctly, the formulas will automatically be enclosed in curly
brackets.

If there are only 8 names to match C1, the additional rows containing
your formula will return a #NUM! error.

If no errors are returned, you really don't know for sure that every
instance of the name was returned, so you should extend your formula rows in
order to insure that *all* the names are returned.

This is why you will *always* want to see at least one error !
 
A

Alan Beban

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you can
use =VLookups("John",a1:b200,2) array entered into at least enough cells
vertically to accommodate the number of occurrences of John.

Alan Beban
 
Top