[polanskygreg1985]

I have a dataset with basis point information for four different price index's on different dates. I need some sort of function that will automaticaly look up a value for a particular index on a particular date. Any help?

Also, lets say i need to find the difference between the basis points of two cells, automatically by putting in the date and the two indexes, do i have to use macros for that?

Appreciate any guiding light on the subject.
Greg

[etaf]

vlookup will use the date and return any of the 4 indexs

Not sure I understand

basis point information for four different price index's on different dates

I see a DATE - unique only 1 date per row
and 4 sets of numbers
FLCG BPS
FLZ1 BPS
FLZ2 BPS
FLZ3 BPS

So what are
basis point information
Price index

so you can use vlookup to choose a date and then use column_index_number to return one of the four columns

can you explain a bit further and maybe give some examples of what you input and what the output is

you can subtract vlookup values

[polanskygreg1985]

The numbers in the table are Basis points, or the difference between these indexes and a larger index. I already calculated the BPS. I just need it to be easy for the user to find what their looking for


Ideally, i would like the user to input the date and index (FLCG, FLZ1, FLZ2, FLZ3) they are interested in, and the output to be the corresponding cell. So if they input "FLZ1 BPS on 1/6/2008", the output is "-.135".

Another issue is automatically calculating the difference between basis points. So the input would be the date and the two indexes, and the output would be the difference between those two figures. So if they input "FLCG BPS & FLZ1 on 1/6/2008", the output would be "-.855".

Thanks

[etaf]

probably needs a macro to make it neat

but as a test, vlookup will do the trick
I have modified your spreadsheet

On sheet1
Cell B5 - you enter a date
Cell C5 - drop down for the indexs
Cell E5 - givesthe answer

the answer use a Vlookup to look up the date from the date entered
it also uses the validation in M2 - M5 to provide the dropdown and is also used for the index required

I'll have a play later today and do the difference

But this is not as elegant as a macro with input boxs

[etaf]

I have added the difference calc

I will explain the process later, as I really MUST go out and should not have been here this morning

[polanskygreg1985]

its pretty amazing that you guys do this for free,... i was just about to "rentacoder" for 20$. That site works great but I like this better. i donated 20$ but do you guys get any of that? and if I wanted to use "ETAF" for projects through paypal, is that possible. Just asking for future reference.

Thanks by the way

[etaf]

I assume the code worked as required. and you followed the process and logic OK

we are volunteers, speaking for myself I get the kick out of helping people,I alos like the challenge of an excel problem, BUT I'm not an expert, I just like this type of data manipulation, and have produce a lot of material in past for operational performance improvement from MI
I also help a lot of people family and friends and this forum helps me out with those problems I cannot solve,
Donations keep the site running, so everyone benefits. So thanks for the donation

The business applications forum, do get some quite complex solutions to projects supplied by posters - so just post your projects and see what response you get.

[polanskygreg1985]

will do and donate, thanks