Would like to learn how to reference data from a large table, please.

Greetings-

I've looked into VLOOKUP, HLOOKUP, Tables, Pivot Tables...and either I'm just not getting it, or what I'm needing is something completely different.

I've attached a file that shows a data table smaller than I will be using, but for this purpose, I think it will hopefully show what I'm trying to ask.

I have a table that has a list of Specific Gravity values in Column A. Across Row 2, it has a list of Temperatures in Celcius.

Although I referenced the next few cells on the same Sheet, my final spreadsheet will have the table on one Sheet, and the values entered into the gray cells, will be on another sheet.

I would like to enter the Specific Gravity in D41, the Temperature in D42, and have a formula or something to enter the Correction Value in D43.

I hope that makes sense. Can someone help me out? I appreciate your help in advance.

Data Table.xls



-Bryan

Also, I put this in formulas, since I think that is ultimately what I'm looking for. My apologies if I put it in the wrong folder.


-Bryan

Correction Values.xls

I've added the actual table I'm working with. I used the code (from FDibbins) in the worksheet attached, and it works....up to a certain point.

I can reference the first 16 Rows (value of 2.300 - 2.315) for it to "MATCH". After that, it gives a #N/A. Will it only work for 16 Rows? I have 801 Rows I need to work with, and also 8 Columns. If you look at the attachment, chnge the grey box to a value greater than 2.315, and yuo will see my problem.

I thought about turning the table on it's side, but I have Office XP, and it only allows 256 columns.

Is there someway to make it reference all 801 rows?


I extremely appreciate the help!! Thank you!!



-Bryan

FDibbins, would you mind looking over this again for me, plz. I'm having new issues, detailed above. Thank you for your help, btw.


-Bryan

Couple of things I noticed. One is that in Column A after Row 18 you started to use more than 3 decimal places, example A19 is 2.31600000000001. When you type in 2.316 in O4 it isn't an exact match so you get the #n/a error. Then in your drop downbox for 6 your using '6 but in J2 you're just using 6... again not an exact match. After you change those two things you should be good to go.

Originally Posted by profector

Couple of things I noticed. One is that in Column A after Row 18 you started to use more than 3 decimal places, example A19 is 2.31600000000001. When you type in 2.316 in O4 it isn't an exact match so you get the #n/a error. After you change those two things you should be good to go.

I corrected the problem, although I don't know why it happened. I put 2.300 in Cell A3 and 2.301 in Cell A4. It actually shows 2.3 in A3. I formatted all cells to a number with 3 digits. I highlighted both cells, then grabbed the lower right-hand corner and copied that trend all the way down to 3.100.

Not too far down the line, it decided to put 2.31600000000001 in the cell. Why did it do that? I just about had to hand type all 801 numbers, to keep it from pasting the values like that. What was Excel doing to my numbers?


-Bryan

I'm not sure why that happen, never seen it before, but glad to hear that it's working for you now.

Originally Posted by profector

I'm not sure why that happen, never seen it before, but glad to hear that it's working for you now.

Will you open a blank spreadsheet, put 2.3 on top of 2.301 and drag it a few hundred cells, and let me know if your version does it as well?

Thank you for all your help.