Lookup Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?
 
One way using non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X,
from row2 down in cols A to E, with col A = key col (Purchase Date)

In another sheet: Y (say),

Paste the same col headers into A1:E1
Let's say G1 will house the input of the purchase date (of interest)

Put in A2:
=IF(ROW(A1)>COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 to E2

Put in F2:
=IF(X!A2="","",IF(X!A2=$G$1,ROW(),""))
(Leave F1 empty)

Then just select A2:F2 and copy down to cover the max expected extent of
data in X. Format cols A & B as dates. Hide away the criteria col F if
desired. Cols A to F will return the required results dynamically from X, ie
only the lines with purchase dates equal to that input in G1. All results
will be neatly bunched at the top.
 
Max....
I think I figured out what the problem was....
Will this formula only work if the column headers are in row 1?
When I delete all the other header information....everything works.
Can I not have the same thing work if my column headers start on row 7 for
both sheets?
Thanks again
 
Max,
Thanks very much for your assistance....I think this is exactly what I
need...but for some reason I still can't get it to work correctly.
I did a test using the example you listed below....but when I try the same
thing with the actual spreadsheet, my data field come up blank.
The only revisions I made to your example below is the following:
My Column Headers run from column A7 to Column J7.
I then have column K represent column F in your example
And instead of putting my date in G1...I have it in C3 instead.

My formulas in column a thru J are:
=IF(ROW(A7)>COUNT($K:$K),"",INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A7)),$K:$K,0))) ....The A's change based on the column this formula is in.

My formula in column K is
=IF(Investments!A7="","",IF(Investments!A7=$C$3,ROW(),""))

Any idea why this still isn't working?

Thanks again...I really appreciate it!!!
 
Can I not have the same thing work
if my column headers start on row 7 for both sheets?
Click to expand...

Presume you mean if source data starts in row7 (instead of in row2). Yes, of
course it can still work but we'd need to make an arithmetic adjustment in
the extract formula.

Here's a working sample configured to suit for easy reference:
http://cjoint.com/?iqiAC6FnX8
Filtering from another sht.xls

Source sheet is named: Investments,
data in cols A to J, headers in A6:J6, data from row7 down

In a new sheet, Y (say)

[Purch] Date of interest will be input in L1
(or the date could be selected from a DV in L1, say)

In A2:
=IF(ROW(A1)>COUNT($K:$K),"",
INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$K:$K,0)+5))
Copy A2 to J2

[The "+5" is an arithmetic adjustment as source data starts in row7, and we
are pulling it from row2 here in Y. ROW(A1) is always used in the starting
formula cell, irrespective of where this start cell may be. ROW(A1) is used
as an incrementer in the formula.]

In K2:
=IF(Investments!A7="","",IF(Investments!A7=$L$1,ROW(),""))

Select A2:K2, fill down to cover the max expected extent of data in
Investments.
Format cols A & B as dates

---
 
Max,
Thanks very much for your assistance....
For some reason I still can't get this to work correctly.
I've done everything you suggested...and it works perfectly with a test
file....but when I try to apply the formulas to my actual spreadsheet, the
data keeps coming up blank.
I can't seem to figure out what I'm doing wrong.
I'll continue to play around with it...because I know it should work and
your suggestion is exactly what I need.

Thanks again
 
Perhaps the source purchase "dates" in Investments' col A aren't real dates
recognized by Excel.

Try this on a spare copy of your actual data ..

Select only the "dates" range in Investments' col A
Click Data > Text To Columns
Click Next > Next to proceed to step 3 of the wizard
In step 3, under "Col data format", check "Date",
then select the appropriate date format from the droplist, eg: MDY
Click Finish

The above should convert all the dates to real dates recognized by Excel,
and everything should work fine now.

Alternatively, we could also give this a try ..

Put instead in K2, and fill down:
=IF(Investments!A7="","",IF(Investments!A7+0=$L$1,ROW(),""))

The "+0" may suffice to coerce the text dates in Investments' col A to real
dates
Let me know how it goes ..

---
Brian said:
Max,
Thanks very much for your assistance....
For some reason I still can't get this to work correctly.
I've done everything you suggested...and it works perfectly with a test
file....but when I try to apply the formulas to my actual spreadsheet, the
data keeps coming up blank.
I can't seem to figure out what I'm doing wrong.
I'll continue to play around with it...because I know it should work and
your suggestion is exactly what I need.

Thanks again

Max said:
Here's a more enduring <g> link to the working sample:
http://savefile.com/files/5594819
Filtering from another sht.xls


---
Click to expand...
Max said:
Can I not have the same thing work
if my column headers start on row 7 for both sheets?
Click to expand...

Presume you mean if source data starts in row7 (instead of in row2). Yes,
of
course it can still work but we'd need to make an arithmetic adjustment in
the extract formula.

Here's a working sample configured to suit for easy reference:
http://cjoint.com/?iqiAC6FnX8
Filtering from another sht.xls

Source sheet is named: Investments,
data in cols A to J, headers in A6:J6, data from row7 down

In a new sheet, Y (say)

[Purch] Date of interest will be input in L1
(or the date could be selected from a DV in L1, say)

In A2:
=IF(ROW(A1)>COUNT($K:$K),"",
INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$K:$K,0)+5))
Copy A2 to J2

[The "+5" is an arithmetic adjustment as source data starts in row7, and
we
are pulling it from row2 here in Y. ROW(A1) is always used in the starting
formula cell, irrespective of where this start cell may be. ROW(A1) is
used
as an incrementer in the formula.]

In K2:
=IF(Investments!A7="","",IF(Investments!A7=$L$1,ROW(),""))

Select A2:K2, fill down to cover the max expected extent of data in
Investments.
Format cols A & B as dates

---
Brian said:
Max....
I think I figured out what the problem was....
Will this formula only work if the column headers are in row 1?
When I delete all the other header information....everything works.
Can I not have the same thing work if my column headers start on row 7
for
both sheets?
Click to expand...
Click to expand...
 
That did it Max!
I used your suggestion and now everything has worked perfectly!!!
Thanks again for your help!!!! I never would've been able to do it without
you!

Max said:
Perhaps the source purchase "dates" in Investments' col A aren't real dates
recognized by Excel.

Try this on a spare copy of your actual data ..

Select only the "dates" range in Investments' col A
Click Data > Text To Columns
Click Next > Next to proceed to step 3 of the wizard
In step 3, under "Col data format", check "Date",
then select the appropriate date format from the droplist, eg: MDY
Click Finish

The above should convert all the dates to real dates recognized by Excel,
and everything should work fine now.

Alternatively, we could also give this a try ..

Put instead in K2, and fill down:
=IF(Investments!A7="","",IF(Investments!A7+0=$L$1,ROW(),""))

The "+0" may suffice to coerce the text dates in Investments' col A to real
dates
Let me know how it goes ..

---
Brian said:
Max,
Thanks very much for your assistance....
For some reason I still can't get this to work correctly.
I've done everything you suggested...and it works perfectly with a test
file....but when I try to apply the formulas to my actual spreadsheet, the
data keeps coming up blank.
I can't seem to figure out what I'm doing wrong.
I'll continue to play around with it...because I know it should work and
your suggestion is exactly what I need.

Thanks again

Max said:
Here's a more enduring <g> link to the working sample:
http://savefile.com/files/5594819
Filtering from another sht.xls


---
Click to expand...
Max said:
Can I not have the same thing work
if my column headers start on row 7 for both sheets?
Click to expand...

Presume you mean if source data starts in row7 (instead of in row2). Yes,
of
course it can still work but we'd need to make an arithmetic adjustment in
the extract formula.

Here's a working sample configured to suit for easy reference:
http://cjoint.com/?iqiAC6FnX8
Filtering from another sht.xls

Source sheet is named: Investments,
data in cols A to J, headers in A6:J6, data from row7 down

In a new sheet, Y (say)

[Purch] Date of interest will be input in L1
(or the date could be selected from a DV in L1, say)

In A2:
=IF(ROW(A1)>COUNT($K:$K),"",
INDEX(Investments!A:A,MATCH(SMALL($K:$K,ROW(A1)),$K:$K,0)+5))
Copy A2 to J2

[The "+5" is an arithmetic adjustment as source data starts in row7, and
we
are pulling it from row2 here in Y. ROW(A1) is always used in the starting
formula cell, irrespective of where this start cell may be. ROW(A1) is
used
as an incrementer in the formula.]

In K2:
=IF(Investments!A7="","",IF(Investments!A7=$L$1,ROW(),""))

Select A2:K2, fill down to cover the max expected extent of data in
Investments.
Format cols A & B as dates

---
Brian said:
Max....
I think I figured out what the problem was....
Will this formula only work if the column headers are in row 1?
When I delete all the other header information....everything works.
Can I not have the same thing work if my column headers start on row 7
for
both sheets?
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

duplicate results in query 6
Lookup with multiple results 4
List data 3
Macro or Formula to merge data 6
How to pull only dates within a requested dynamic range 3
Advance Macro help 2
Preventing a listing of duplicate records 1
Vlookup help please 1

Back
Top