HELP WITH EXCEL

[HUSKER55]

I have some data that I need to pull from different databases. What I would like to do is have the computer look up the database then lookup the data I request.

a B c etc
1-2 1-2 1-2

I want the program to search thru a to c and return the value in column 2 after it matches my criteria in column one


I am doing something wrong but I don't know what. Right now I have a headache and 40 if statements is more than I care to deal with.

any help would be appreciated

thanks

[slewis]

I have some data that I need to pull from different databases. What I would like to do is have the computer look up the database then lookup the data I request.

a B c etc
1-2 1-2 1-2

I want the program to search thru a to c and return the value in column 2 after it matches my criteria in column one


I am doing something wrong but I don't know what. Right now I have a headache and 40 if statements is more than I care to deal with.

any help would be appreciated

thanks


TRY THE "HLOOKUP" FUNCTION...handles the task easily...if I understand you correctly.

[raybo]

I have some data that I need to pull from different databases. What I would like to do is have the computer look up the database then lookup the data I request.

a B c etc
1-2 1-2 1-2

I want the program to search thru a to c and return the value in column 2 after it matches my criteria in column one


I am doing something wrong but I don't know what. Right now I have a headache and 40 if statements is more than I care to deal with.

any help would be appreciated

thanks

Don't understand what you're layout looks like.

[Tom]

Husker, YES! Lookup, one of them, will do what you need.
I saved myself from the dreaded If,Then over and over and over with lookup.
"IF you use lookup, THEN this is your last If/Then command!" ;)

[raybo]

Husker, YES! Lookup, one of them, will do what you need.
I saved myself from the dreaded If,Then over and over and over with lookup.
"IF you use lookup, THEN this is your last If/Then command!" ;)

There are some requirements when using "Lookup, VLookup, and HLookup", like values must be in ascending order, he may not be able to use it without doing a "sort-ascending" first.

A better description of his data (rows and columns) would be a big help in determining how to return the value he wants.

[HUSKER55]

What I am trying to do is make a parallel time chart similar to the one in Beyer's book "Picking the winners" to project a time.

I need the computer to pick the aproriate race length and then search that DB for the time and then insert the appropriate speed rating on the putput page.

I don't know what happened but I can not seem to make anything work. Somewhere between my head and my keyboard it is getting lost.

"a b c" are names of db fields (length of races)
1 is the time and 2 is the speed rate

Thanks

[slewis]

There are some requirements when using "Lookup, VLookup, and HLookup", like values must be in ascending order, he may not be able to use it without doing a "sort-ascending" first.

A better description of his data (rows and columns) would be a big help in determining how to return the value he wants.


I often implement H and V lookup when sorting comma delimted chart files and data need not be in ascending or descending order. It returns the value in the adjacent cell when pinging the value queried.

[slewis]

ok HUSK,


hAPPY tHANKSGIVING....

Try this: (took me 1 minute)

YOu need to have a variable.. lets say time of the race.

In cell A1 enter 111.3

in cell A2 type the following:

=VLOOKUP(A1, D1:E7, 2)

IN COLUMN D:

IN CELL D1 TYPE 110.4
IN CELL D2 TYPE 111.1
IN CELL D3 TYPE 111.2
IN CELL D4 TYPE 111.3
IN CELL D5 TYPE 111.4
IN CELL D6 TYPE 112
IN CELL D7 TYPE 112.1

now IN Column E:

IN CELL E1 TYPE 84
E2 ....... TYPE 81
E3 ...........TYPE 78
E4 ......... TYPE 75
E6 ...........TYPE 71
E7 ..........TYPE 68

The program will search the d column for "111.3" and return the value of 75.

NOW... input 111.1 in column A1 and notice the change in cell A2....

You should see "81" or the value in cell E2.

you can of course increase the columns as you see fit.

Also.. use an "IF" statement to separate distances, for example:

put a one mile time in A1... then make two new columns for 1mile times and they're corresponding beyer or whatever figures (like columns "E and D")
Write a new VLOOKUP formula for those columns....
You're good to go...!

[slewis]

PS HUSK,

In case you haven't noticed, you need to type ALL the data and columns I gave for the spredsheet to return a value in KEY CELL A2.
In other words, after typing the formula in A2, you'll get an error until you fill data in the other columns.. so keep typing and watch Bill Gates perform his magic.

(I'm sure you knew this but... what the heck.)

[HUSKER55]

Thanks guys


BTW,...I wished I knew this before :D

thanks again

[raybo]

Ok, it looks like your have 2 worksheets, Sheet1 has the race distance and time you want to lookup and a place for the outputted speed rating, Sheet2 is your database of available distances and their corresponding available times and speed ratings.

The outputted speed rating cell in Sheet1 would have to have a nested "IF" statement like: "=IF(Sheet1distance=5.5,Lookup(Sheet1time,Sheet2Ran geFor5.5fTimes,Sheet2RangeFor5.5f speed ratings), IF(Sheet1distance=6,Lookup(Sheet1time,Sheet2RangeF or6fTimes,Sheet2RangeFor6f speed ratings),IF(Sheet1distance=6.5,Lookup(Sheet1time,S heet2RangeFor6.5fTimes,Sheet2RangeFor6.5f speed ratings),IF(......),etc)))....".

If you are using Excel 2007 you can put the whole nested "IF" statement in one cell, if using previous versions of Excel you can only put 7 or 8 nested "IFs" in one cell, you will need to reference at the end of 7 or 8 "IFs" the continuing cell for the remainder of the formula.

Note: Sheet2times must be in ascending order.

[HUSKER55]

THANKS RAY, APPRECIATE IT.

[raybo]

I have a par times chart for many tracks, dirt and turf, and all the distances run at each track. In my main sheet I concatenate the track code,distance,and surface code ("=concatenate(TrackCode,Distance,Surface)". In my par times chart I also enter the concatenated code for each track, distance and surface.

Example:

The concatenated code in my main sheet for a race at Houston, 6f, dirt would be "Hou6.00D".

In the chart where the par time for Houston, 6f, dirt is located I enter "Hou6.00D" on the same row and adjacent to the rest of the chart. The codes are sorted in ascending order.

Then to get the par time for any track, any distance run at that track, and any surface run there, I simply do a "Lookup" on the code and have it return the par time. No need for any nested "IFs" this way.

For you to do this you would need to include, in the concatenation, the time, like: Hou6.00D111.3" or "Hou6.00D71.3". The same type code would have to be entered next to the speed figures for each track, distance, and surface.

Your database sheet would have a column for the codes, and a column for the speed figures.
Example:

Codes ......................Speed Ratings
Hou6.00D70.0 ............90
Hou6.00D70.1 ............88
Hou6.00D70.2 ............86
Hou6.00D70.3 ............84
Hou6.00D70.4 ............82
Hou6.00D71.0 ........... 80
Hou6.00D71.1 ............78
Hou6.00D71.2 ............76
Hou6.00D71.3 ............74
Hou6.00D71.4 ............72
Hou6.00D72.0 ............70

Do a "Lookup" like: "=Lookup(Sheet1Code,Sheet2RangeForCodes,Sheet2Range ForSpeedRatings)"

This would mean a little work but would allow you to do a single "Lookup" and would include more than 1 track.

[HUSKER55]

Great idea, Thanks !:ThmbUp: