Search one worksheet to pull data into another worksheet

[HyperMite]

Worksheet 1:
Name Acct Symbol Desc Qty Price Value
Smith 614 FDRXX Fid Cash 33.66 1.00 33.66
Jones 188 FDRXX Fid Cash 55.00 1.00 55.00
SmithA 617 FCNTX Fid Contra 292.29 42.85 12524.67
Jones 188 FCNTX Fid Contra 273.98 42.85 11739.96

Worksheet 2:
I would like to first search Worksheet 1 for all instances of acct 188 which
can appear anywhere in column 2, (in above example there are two results)
then find those with symbol FCNTX (down to one result, and return Fid Contra
273.98, 42.85, and 11739.96.

Worksheet 1 always has the same columns but in random order. It can be
sorted but my rows vary from 10 to 100 depending on the day.

Each name has a unique acct number, so Jones is always 188.

I've tried Lookup, Match, If, VLookup all with some limited success but not
really getting what I want. If someone can tell me IF this can be done (it
seems obvious that it should be able to), and then what function I should be
using, it would be helpful. Thanks very much in advance.

- Barbara

 

[Shane Devenshire]

Hi,

Enter this array formula

=INDEX(sheet1!D25,MAX(ROW(sheet1!B2:B5)*(sheet1!$B$2:$B$5=188)*(sheet1!$C$2:$C$5="FCNTX"))-1)

And copy it to the right for three more columns. To make it an array you
must press Shift+Ctrl+Enter to enter it.

 

[HyperMite]

Thanks Shane. I appreciate the help, and now I just have to get this to sink
into my thickened brain. The reference D25 is the range of the column?
Same with B2:B5? Of course my data files are much larger but I'm going to
simplify it and try again with your formula.

The formula has to be put into each cell in worksheet 2? + 1 for each column?

 

[HyperMite]

My thickened brain apparently does not yet understand the syntax of the Index
function. I'm going back to basics here just to make sure I understand what
I have and what I'm trying to get.

Worksheet1 is the data, with account number being the first index (Column
A), and fund symbol (Column B) being the second index, and then a whole bunch
of other columns (C - G) holding other data unique to the account number and
the fund. In my original post I put a client name in Column A, but that is
not necessary.


Worksheet 2 is the printed report. Other data from another worksheet
(Worksheet 3) is pulled into Worksheet 2 and automatically updated. No
problems there because it is a lookup based on a fund number where the fund
number equals the row number. Fund #63 has a price of 2.56 and the formula
in worksheet 2 references cell G63. If I manually make a change to worksheet
3, let's say change the price from 2.56 to 2.47, worksheet 2 updates
perfectly. While the numeric data in worksheet 3 changes all the time, Row
63 always relates to Fund 63 and is always kept in Column G.

Hmmmm, says I. I have a Worksheet 1 with all the data I need to further
populate Worksheet 2, but it is never a defined number of rows. The columns
remain the same (Account Number is always Column A, Fund Symbol is always
Column B, etc). Account number to Fund Symbol is a one to many relationship.
One account number can have 10 Fund Symbols.

Can I write a formula in Worksheet 2 that says "Hey look through all the
column A data and find me a specific account number. When you have found it,
look for that account's fund symbol in column b XXXXX, and populate worksheet
2 with the info stored in columns c, d, e, f, and g. Worksheet 2 is
completely different from worksheet1 and the data from worksheet 1 actually
goes into different cell addresses. Worksheet 1 C10 data may go into
Worksheet 2 H14, D10 goes into H17, E10 goes into J4.....

Now with all that said, and assuming you are still awake out there, does the
INDEX function actually do a multilevel search? And secondly, since the
number of rows in Worksheet 1 constantly changes, do I have to update the
formulas in Worksheet 2 with the new ranges everytime I get a new Worksheet 1?

Here's hoping I make sense and someone can point me to the right way.
Shane, I was successful with your formula to a certain extent but I lost you
when you were talking about the copying of the formula since it was an array
with Ctl Shft Enter. Maybe you can enlighten me? I was thinking of using
Access or maybe a Query if the formula load gets too complicated.

To give you an idea of volume: I have one Worksheet1 generated monthly, one
Worksheet 3 which has price changes on it sometimes daily, and 50 separate
Worksheet 2's which need to get their data from Worksheet1 and Worksheet3.

Shane, thank you for this:

=INDEX(sheet1!D25,MAX(ROW(sheet1!B2:B5)*(sheet1!$B$2:$B$5=188)*(sheet1!$C$2:$C$5="FCNTX"))-1)

 

[HyperMite]

One other small piece of data. The account number is stored in Worksheet2,
so I wonder if the formula I write to pull the data can compare (MATCH?) the
account number in Column A of Worksheet1. While I don't have it set up this
way, I can put the Fund Symbol Column B in Worksheet2 if I need to.

Great....thanks. Really. This is what happens to an old lady who worked
with Visicalc on a Apple II Plus, and on a TRS-80, and stayed in the IT
ndustry for 20 years and finally retired. Now I'm on a second career...

 

[HyperMite]

This is what I have used so far with the most success:

=INDEX([FidBalances.xlsx]Sheet1!A1:H25,MATCH("614-199435",[FidBalances.xlsx]Sheet1!B1:B25,0),4)

Of course, it varies quite a bit from the INDEX only solution that Shane
provided, but I have been unsuccessful in following that syntax. The
advantage is that Shane's formula allowed for two lookups. Is there any way
to add a second MATCH statement to the above? I'm trying this...

"=INDEX([FidBalances.xlsx]Sheet1!A1:H25,MATCH("614-199435",[FidBalances.xlsx]Sheet1!B1:B25,0)*(MATCH("FCASH",[FidBalances.xlsx]Sheet1!C1:C25,0)),3)

but it is not successful. It returns error #REF.

 

[HyperMite]

Update: Early a.m. and I'm still at it. First, I get the Ctrl+Shift+Enter
bit, so while it may not show up on this formula, I do see the braces in my
actual spreadsheet. Now this is where I am:

{=INDEX(([FidBalances.xlsx]Sheet1!$A$1:$H$25)*(MATCH("614-199435",[FidBalances.xlsx]Sheet1!$B$1:$B$25,0))*(MATCH("FCASH",[FidBalances.xlsx]Sheet1!$C$1:$C$25,0)),4)}

Return is #VALUE!

Alternately, removing some of the parentheses, and the first *:

{=INDEX([FidBalances.xlsx]Sheet1!$A$1:$H$25,MATCH("614-199435",[FidBalances.xlsx]Sheet1!$B$1:$B$25,0)*MATCH("FCASH",[FidBalances.xlsx]Sheet1!$C$1:$C$25,0),4)}

Return is #REF

Either way, I don't think the formula sees the second MATCH lookup.

Any suggestions...?

This is what I have used so far with the most success:

=INDEX([FidBalances.xlsx]Sheet1!A1:H25,MATCH("614-199435",[FidBalances.xlsx]Sheet1!B1:B25,0),4)

Of course, it varies quite a bit from the INDEX only solution that Shane
provided, but I have been unsuccessful in following that syntax. The
advantage is that Shane's formula allowed for two lookups. Is there any way
to add a second MATCH statement to the above? I'm trying this...

"=INDEX([FidBalances.xlsx]Sheet1!A1:H25,MATCH("614-199435",[FidBalances.xlsx]Sheet1!B1:B25,0)*(MATCH("FCASH",[FidBalances.xlsx]Sheet1!C1:C25,0)),3)

but it is not successful. It returns error #REF.

One other small piece of data. The account number is stored in Worksheet2,
so I wonder if the formula I write to pull the data can compare (MATCH?) the
account number in Column A of Worksheet1. While I don't have it set up this
way, I can put the Fund Symbol Column B in Worksheet2 if I need to.

Great....thanks. Really. This is what happens to an old lady who worked
with Visicalc on a Apple II Plus, and on a TRS-80, and stayed in the IT
ndustry for 20 years and finally retired. Now I'm on a second career...