Lookups

tony0682

Board Regular
Joined
May 19, 2005
Messages
164
I have a table that looks like the below.
bb bond data 02282009.xls
BCDEF
402/28/09
531394U4L3FNM05099AF103,333,333.0061,047,515.50
631395DUX5FNM06042PF135,000,000.0089,288,725.95
731395NCU9FNM06044FP260,000,000.00166,083,889.40
Summary


Currently, I have to manually enter data into cell F5 from data from the chart below. I need a formula lookup the CUSIP and return data from the month/year for that security. This is only a sample set of data.. There are 50 securities I have to do this for. The data chart below has to be displayed that way since they are generated from data feeds and go across for 50 securities.
bb bond data 02282009.xls
ABCDEFGH
131394U4L331395DUX531395NCU9
231394U4L3Mtge31395DUX5Mtge31395NCU9Mtge
3MTG_HIST_FACTMTG_HIST_FACTMTG_HIST_FACT
43/25/20090.581760883/25/20090.648196163/25/20090.62481068
52/25/20090.590782412/25/20090.661397972/25/20090.63878419
61/25/20090.594603281/25/20090.66933721/25/20090.64863839
Factor



Any help is appreciated.

Thanks.

Tony
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hello,

this gives the answers in F5, F6 and F7

=E5*OFFSET(FACTOR!$A$1,MATCH(SUMMARY!$G$4,FACTOR!A:A,-1),MATCH(SUMMARY!$B5,FACTOR!$1:$1,0))

put this in F5 and copy down.
 
Upvote 0
Sorry. I forgot to mention that the date in F4 is 2/28/09 while the dates on the sheet I'm trying to look up has a different date. So therefore, I need to match the month and the year for it to look it correctly. How should the formula be modified?
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top