Create a row listing based on a lookup value
 

I want to allow a user to enter a data value in a cell, then create a row
listing based on looking up the user entered value in a table array.

I have the following sample data listed on the "Data" tab of a workbook.

ASR TABLE
AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType
ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN
ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN
ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN

The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2.

Thus, on a second tab, named "User Entry", the user will enter a SecIDKey
value in a cell (A1), and then I want the formula to create a row listing of
the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4).

I've seen several posts that hint at how to do this, but haven't found one
yet that explicitly defines how to accomplish this.

Thanks in advance for assistance.

--
Thanks,
Jen

Create a row listing based on a lookup value
 

Take a look on Advanced Filter in Help menu

"JBush" wrote:

I want to allow a user to enter a data value in a cell, then create a row
listing based on looking up the user entered value in a table array.

I have the following sample data listed on the "Data" tab of a workbook.

ASR TABLE
AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType
ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN
ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN
ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN

The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2.

Thus, on a second tab, named "User Entry", the user will enter a SecIDKey
value in a cell (A1), and then I want the formula to create a row listing of
the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4).

I've seen several posts that hint at how to do this, but haven't found one
yet that explicitly defines how to accomplish this.

Thanks in advance for assistance.

--
Thanks,
Jen

Create a row listing based on a lookup value
 

Thanks for the tip, but I'm not looking to filter.

I specifically want to allow the user enter data (the SecIDKey = SID2) on
the "User Entry" tab, and then have a formula that will lookup the
corresponding rows on the "Data" tab where SecIDKey = SID2, and then populate
those rows on the "user Entry" tab.

Make sense??
--
Thanks,
Jen


"Teethless mama" wrote:

Take a look on Advanced Filter in Help menu

"JBush" wrote:

I want to allow a user to enter a data value in a cell, then create a row
listing based on looking up the user entered value in a table array.

I have the following sample data listed on the "Data" tab of a workbook.

ASR TABLE
AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType
ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN
ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN
ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN

The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2.

Thus, on a second tab, named "User Entry", the user will enter a SecIDKey
value in a cell (A1), and then I want the formula to create a row listing of
the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4).

I've seen several posts that hint at how to do this, but haven't found one
yet that explicitly defines how to accomplish this.

Thanks in advance for assistance.

--
Thanks,
Jen

Create a row listing based on a lookup value
 

One way which delivers using non-array formulas ..

In sheet: Data,

Assume data in cols A to G, from row3 down,
where col A = AcctSecRefKey
col C = SecIDKey

In sheet: User Entry,

Assume the input for SecIDKey will be made in B1

Put in A2;
=IF(OR($B$1="",Data!C3=""),"",IF($B$1=Data!C3,ROW( ),""))
(Leave A1 blank)

Put in B2:
=IF(ROW(A1)COUNT(A:A),"",INDEX(Data!A:A,SMALL(A:A ,ROW(A1))+1))

Select A2:B2, copy down to cover the max expected extent of data in "Data".
Hide away col A. Col B will return the required results for the input in B1,
all neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JBush" wrote:
Thanks for the tip, but I'm not looking to filter.

I specifically want to allow the user enter data (the SecIDKey = SID2) on
the "User Entry" tab, and then have a formula that will lookup the
corresponding rows on the "Data" tab where SecIDKey = SID2, and then populate
those rows on the "user Entry" tab.

Make sense??
--
Thanks,
Jen


"Teethless mama" wrote:

Take a look on Advanced Filter in Help menu

"JBush" wrote:

I want to allow a user to enter a data value in a cell, then create a row
listing based on looking up the user entered value in a table array.

I have the following sample data listed on the "Data" tab of a workbook.

ASR TABLE
AcctSecRefKey.....AcctKey..........SecIDKey.....Re g.....Loc.....SecID.....SecIDType
ASR1..........AK1..........SID2..........REG1..... BBH.....ID2.....ISIN
ASR2..........AK2..........SID2..........REG1..... SSB.....ID2.....ISIN
ASR3..........AK3..........SID2..........REG1..... DB.....ID2.....ISIN

The user wants to create a list of AcctSecRefKeys WHERE the SecIDKey = SID2.

Thus, on a second tab, named "User Entry", the user will enter a SecIDKey
value in a cell (A1), and then I want the formula to create a row listing of
the corresponding AcctSecRefKeys (i.e., ASR1, ASR2, ASR3 in cells A2:A4).

I've seen several posts that hint at how to do this, but haven't found one
yet that explicitly defines how to accomplish this.

Thanks in advance for assistance.

--
Thanks,
Jen

Create a row listing based on a lookup value
 

Good to hear that, Jen !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"JBush" wrote:
Thanks, Max!!

That did exactly what I needed - plus, I got to learn some new Excel
tricks...which I always like!

I appreciate the help!
--
Thanks,
Jen