Multiple responses from a look up table
 

maybe some more info on how the data you're looking up is laid out.

--


Gary


"mickn74" wrote in message
...
I want to key in a number to return multiple responses. Im using a look up a
table on a secondary sheet yet the number has multiple responses. What
formula do I use or where in help is it. I found look up One value and return
multiple corresponding Values yet I need to return multiple worlds not
numerical values.

Can anoyone point me in the right direction

Cheers

Michael

Multiple responses from a look up table
 

Garry

Thanks for you time more info

Sheet 1 entering the data of a post code 4000 on sheet 1, I want it to
return QCBD/QBMH and QBMH by typing in a single post code

Sheet 2 is the table listed below it needs to search the post code and
return multiple values say QCBD/QBMH and QBMH as there are 2 * 4000 postcode

4000 QCBD/QBMH Brisbane
4000 QBMH Spring Hill
4004 QBMH All suburbs
4005 QBMH All suburbs
4006 QBMH/QAGW All suburbs
4007 QSPN All suburbs
4008 QSPN All suburbs

I hope that sheds some light on it

Cheers,

Mick

"Gary Keramidas" wrote:

maybe some more info on how the data you're looking up is laid out.

--


Gary


"mickn74" wrote in message
...
I want to key in a number to return multiple responses. Im using a look up a
table on a secondary sheet yet the number has multiple responses. What
formula do I use or where in help is it. I found look up One value and return
multiple corresponding Values yet I need to return multiple worlds not
numerical values.

Can anoyone point me in the right direction

Cheers

Michael

Multiple responses from a look up table
 

here's some basic code you can maybe modify. not sure what you want to do, but
this will find whatever is on sheet1 a1 in sheet2 column a.

Option Explicit
Sub find_post()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim rng1 As Range, rng2 As Range, rngfound As Range
Dim firstaddress As String
Dim i As Long
Dim lastrow2 As Long
lastrow2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

On Error Resume Next
Set rng1 = ws1.Range("A1")
Set rng2 = ws2.Range("A1:A" & lastrow2)
With rng2
Set rngfound = .Find(What:=rng1, LookIn:=xlValues, lookat:=xlWhole)
If Not rngfound Is Nothing Then
firstaddress = rngfound.Address
Do
rngfound.Offset(0, 1).Resize(1, 2).Copy rng1.Offset(i, 1)
Set rngfound = .FindNext(rngfound)
i = i + 1
Loop Until rngfound.Address = firstaddress
End If
End With
End Sub

--


Gary


"mickn74" wrote in message
...
Garry

Thanks for you time more info

Sheet 1 entering the data of a post code 4000 on sheet 1, I want it to
return QCBD/QBMH and QBMH by typing in a single post code

Sheet 2 is the table listed below it needs to search the post code and
return multiple values say QCBD/QBMH and QBMH as there are 2 * 4000 postcode

4000 QCBD/QBMH Brisbane
4000 QBMH Spring Hill
4004 QBMH All suburbs
4005 QBMH All suburbs
4006 QBMH/QAGW All suburbs
4007 QSPN All suburbs
4008 QSPN All suburbs

I hope that sheds some light on it

Cheers,

Mick

"Gary Keramidas" wrote:

maybe some more info on how the data you're looking up is laid out.

--


Gary


"mickn74" wrote in message
...
I want to key in a number to return multiple responses. Im using a look up a
table on a secondary sheet yet the number has multiple responses. What
formula do I use or where in help is it. I found look up One value and
return
multiple corresponding Values yet I need to return multiple worlds not
numerical values.

Can anoyone point me in the right direction

Cheers

Michael