vlookup variation??

Hello,

If you are prepared to do it manually use DATA, FILTER and check AUTOFILTER, or if you do want it automated, VB is probably the best way to go.

alanoreilly said:

Is it possible to use a vlookup to produce a number of results…

I have a list of surnames, and I want to use the vlookup to find all the Smiths in the list, obviously a basic vlookup only brings back the first Smith it finds….

=vlookup(A1, download,1,false) , where A1 is some sort of search box for users

or is there a simple VB solution??

Click to expand...

Your question is a bit ambiguous. Given a list like:

Smith
Smith
Doe
Smith

I'd just count Smith's with N as result and print out Smith N times.

I suspect that this is not what you want. If not, care to post a small sample and the result that you want to see?

on one sheet there is a list of names and personal details...(here I'm just showing names for ease of use, see list below)

on a second sheet is a search fuinction, If Smith is typed in A1, the vlookup will only give the first Smith on the list below, I want to get all the Smiths on the list

=VLOOKUP(A1,list,1,FALSE)


NAME
murphy
smith
johnson
smith
henry
cash
law
smith
rushton
rolls
smith
hartman
smith


I hope this is clearer

Thanks
Alan

alanoreilly said:

on one sheet there is a list of names and personal details...(here I'm just showing names for ease of use, see list below)

on a second sheet is a search fuinction, If Smith is typed in A1, the vlookup will only give the first Smith on the list below, I want to get all the Smiths on the list

=VLOOKUP(A1,list,1,FALSE)


NAME
murphy
smith
johnson
smith
henry
cash
law
smith
rushton
rolls
smith
hartman
smith


I hope this is clearer

Thanks
Alan

Click to expand...

I suppose you want to retrieve the details too. Right? Otherwise, my initial reply will still apply.

you'll have to excuse my ignorance but I don't know what the following means...

"I'd just count Smith's with N as result and print out Smith N times"

could you dumb it down a little???

thanks
alan

alanoreilly said:

you'll have to excuse my ignorance but I don't know what the following means...

"I'd just count Smith's with N as result and print out Smith N times"

could you dumb it down a little???

thanks
alan

Click to expand...

Lets forget that Alan. Is it your intention also retrieve the details of each Smith?

I'll just assume that you want to retieve details involving a name...

Sheet1 houses the data (the lookup table)

Method 1

A side note. This collapses a number of formula systems of mine for returning multiple values and Just_Jon's (see: http://216.92.17.166/board2/viewtopic.php?t=73228)...

Select A2:D14.
Go to the Name Box on the Formula Bar, type LTable, and hit enter.

Sheet2a

Select B1.
Activate Data|Validation.
Choose List for Allow.
Enter the following in the Source box:

=INDEX(LTable,0,1)

Click OK.

In A2 enter: Pos [ just a label, abbreviated from Position ]

In B2 enter:

=COUNTIF(INDEX(LTable,0,1),B1)

In A3 enter & copy down:

=IF(COUNTA($A$2:A2)<=$B$2,MATCH($B$1,INDEX(Sheet1!A:A,N(A2)+1):INDEX(Sheet1!A:A,MATCH(BigStr,Sheet1!A:A)),0)+N(A2),"")

In B3 enter, copy across, then down:

=IF(N($A3),INDEX(Sheet1!A:A,$A3),"")

Method 2

Sheet2b

In A1 enter: NAME [ a label from LTable ]

Select A2.
Activate Data|Validation.
Choose List for Allow.
Enter the following in the Source box:

-INDEX(LTable,0,1)

Click OK.

Select A1:A2.
Activate Data|Filter|Advanced Filter.
Check Copy to another location.
Enter Sheet1!$A$1:$D$14 in the box for List range.
Enter Sheet2b!$A$1:$A$2 in the box for Criteria range.
Enter Sheet2b!$A$3 in the box for Copy to.
Leave Unique records only unchecked.
Click OK.

The foregoing Advanced Filter is not automatic, although you can record a macro which you can play at will.

thanks, that's perfect, apologies for the delay I had run to a meeting

Thanks again,
Alan

alanoreilly said:

thanks, that's perfect, apologies for the delay I had run to a meeting

Thanks again,
Alan

Click to expand...

For Method 1, you need to define BigStr, which I forgot to include:

Activate Insert|Name|Define.
Enter BigStr in the Names in Workbook box.
Enter the following in the Refers to box:

=REPT("z",255)

Click OK.