question about lookup functions

Deanna,

Would you care a (cooked up) sample of your data along with 2 lookup values? That's most easily done by typing an = sign in an empty cell, selecting 10 rows of your data (including any column headings you may have), and hitting CONTROL+SHIFT+ENTER at the same time. Go to the formula bar that shows the contents of this cell, select the part after the = sign and hit F9. You'll see your sample data to appear between braces. Copy it and paste in the follow up.

Aladin

===========

Aladin,

neat trick - here it is:
{"Trijet","1-25%","26-50%","51-75%","76-100%";"0-25",86,119,147,175;"26-55",119,207,276,341;"56-87",155,291,392,475;"88-125",193,365,504,668;"126-187",239,456,701,950;"188-312",318,673,1076,1476;"+313",406,978,1578,2179}

Thanks!
Deanna

Maybe this will help. You should be able to paste
it into cell A1 and see how index() works. Only
paste what is between the "*"

*******************************************
Row 5 Row 5
Column 2 Column 2
Returns a4 Returns =INDEX($A$5:$E$9,E1,E2)

a b c d
1 a1 b1 c1 d1
2 a2 b2 c2 d2
3 a3 b3 c3 d3
4 a4 b4 c4 d4
*******************************************

Looks like the html didn't keep the formatting.
Copy the example from the commments text box below.

Deanna,

I propose that you edit a bit the organization of your data. Here how it should look:

{"Trijet",25%,50%,75%,100%;0,86,119,147,175;25,119,207,276,341;55,155,291,392,475;87,193,365,504,668;125,239,456,701,950;188,318,673,1076,1476;312,406,978,1578,2179}

Lets say this sample data occupy A1:E8.

In G1 enter: 27 [ a possible lookup value ]
In G2 enter: 52% [ "that's your second lookup value" ]

In G4 enter: =VLOOKUP(G1,$A$2:$E$8,MATCH(G2,$A$1:$E$1,1),1)

The last args of VLOOKUP as well as of MATCH both are 1, which stands for TRUE.

This retrieves 207 as value.

If this is wrong, let me know.

Aladin

==============

Sorry, not sure I follow you. I am supposed to paste:
": Row 5 Row 5 a b c d 1 a1 b1 c1 d1 2 a2 b2 c2 d2 3 a3 b3 c3 d3 4 a4 b4 c4 d4"
into A1 to see how index works?
I did try it, w/ and w/o an "=" sign preceding. Also tryed the "{}" in case it was a trick (like how you had me send the data). Neither worked. I did try Index according to the help info that is in Excel and got a return of "#VALUE".
Here is the matrix (in case this format helps):

Trijet 1-25% 26-50% 51-75% 76-100%
0-25 86 119 147 175
26-55 119 207 276 341
56-87 155 291 392 475
88-125 193 365 504 668
126-187 239 456 701 950
188-312 318 673 1076 1476
+313 406 978 1578 2179

And what I want to do is return the value based on a cell reference to per cent and range of numbers. I tried a long if statement not realizing that there are a max number of arguments.
Thanks!

This doesn't seem to work. Do I need to define the range for each cell in the column/row header somehow? It comes out as "#N/A".
Sorry about the last reply, I am not sure what happened. Maybe 2 people are responding to me?

Sorry about the last reply, I am not sure what happened. Maybe 2 people are responding to me?

IT SHOULD DEANNE. SENDING YOU THE WORKBOOK. AS I SAID, I REORGANIZED YOUR ROW AND COLUMN HEADINGS. LOOK AT THEM CLOSELY BECAUSE YOU MIGHT NEED TO ADJUST THEM THE WAY THEY ARE INTENDED.

Aladin

I emailed you a spreadsheet ": Row 5 Row 5 : Column 2 Column 2 : Returns a4 Returns =INDEX($A$5:$E$9,E1,E2) : : a b c d : 1 a1 b1 c1 d1 : 2 a2 b2 c2 d2 : 3 a3 b3 c3 d3 : 4 a4 b4 c4 d4" into A1 to see how index works? I did try it, w/ and w/o an "=" sign preceding. Also tryed the "{}" in case it was a trick (like how you had me send the data). Neither worked. I did try Index according to the help info that is in Excel and got a return of "#VALUE". Here is the matrix (in case this format helps): 0-25 86 119 147 175 26-55 119 207 276 341 56-87 155 291 392 475 88-125 193 365 504 668 126-187 239 456 701 950 188-312 318 673 1076 1476 +313 406 978 1578 2179