Live Chat & Podcast at 1:00PM Eastern on Sunday!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
Search
Business Applications
Tag Cloud
access acer asus bios bsod computer crash driver drivers error ethernet excel freeze gaming gpu hard drive hardware hdmi internet laptop mac malware memory monitor motherboard music network printer problem ram registry router server slow software sound trojan ubuntu 11.10 uninstall usb video virus vista wifi windows windows 7 windows 7 32 bit windows 7 64 bit windows xp wireless
Search
Search for:
Tech Support Guy Forums > Software & Hardware > Business Applications >
Vlookup?

Reply  
Thread Tools
polanskygreg1985's Avatar
Computer Specs
Member with 39 posts.
 
Join Date: Nov 2009
Experience: Beginner
14-Nov-2009, 05:26 PM #1
Vlookup?
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
Attached Files
File Type: xlsx Index Prices.xlsx (24.9 KB, 35 views)
etaf's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
14-Nov-2009, 05:38 PM #2
vlookup will use the date and return any of the 4 indexs

Not sure I understand
Quote:
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
__________________
Note: I have very limited time during weekdays to visit here, so there will be a delay in replying
UK timezone
Please let us know what the final solution was to any problem posted
polanskygreg1985's Avatar
Computer Specs
Member with 39 posts.
 
Join Date: Nov 2009
Experience: Beginner
14-Nov-2009, 07:24 PM #3
ideally..
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's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
15-Nov-2009, 06:20 AM #4
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
Attached Files
File Type: xls index prices ETAF.xls (68.0 KB, 33 views)
__________________
Note: I have very limited time during weekdays to visit here, so there will be a delay in replying
UK timezone
Please let us know what the final solution was to any problem posted
etaf's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
15-Nov-2009, 06:42 AM #5
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
Attached Files
File Type: xls index prices ETAF-1.xls (68.5 KB, 37 views)
polanskygreg1985's Avatar
Computer Specs
Member with 39 posts.
 
Join Date: Nov 2009
Experience: Beginner
15-Nov-2009, 09:41 PM #6
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's Avatar
Computer Specs
Moderator with 34,408 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
Experience: Intermediate
16-Nov-2009, 05:26 AM #7
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.
__________________
Note: I have very limited time during weekdays to visit here, so there will be a delay in replying
UK timezone
Please let us know what the final solution was to any problem posted

Last edited by etaf; 16-Nov-2009 at 05:37 AM..
polanskygreg1985's Avatar
Computer Specs
Member with 39 posts.
 
Join Date: Nov 2009
Experience: Beginner
16-Nov-2009, 09:10 AM #8
will do and donate, thanks
Reply

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who want to help you solve your computer problems. See our Welcome Guide to get started.
Thread Tools



Facebook Facebook Twitter Twitter TechGuy.tv TechGuy.tv Mobile TSG Mobile
You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 05:16 PM.
Copyright © 1996 - 2011 TechGuy, Inc. All rights reserved.

Powered by Cermak Technologies, Inc.