Index/Match problem
Posted by DS1 on May 17, 2001 11:11 AM
I am working with data, about 13 columns and 270rows, basically i want to know how to find the intersection of the two (date-across columns) and price(down the rows). Then i will use the formula to find the intersection of various inputs, like wk 156 and price of 25%. Could someone please help me, i have reviewed previous postings and found nothing directly applicable to my situation, excetp #13419 i tried, but it didn't work because i only have 1 sheet to reference
Thanks in advance to anyone who can help me...
Posted by IML on May 17, 2001 11:40 AM
Have you tried using the Lookup wizard under Tools - Wizard? This is a great starting point.
Good luck
Posted by Aladin Akyurek on May 17, 2001 11:47 AM
See:
http://www.mrexcel.com/tip021.shtml
& Excel's Help pages.
Still in doubt: post a small portion of your data along with your specific question.
Aladin
Posted by Barrie Davidson on May 17, 2001 11:49 AM
If I understand your requirements correctly, you want to use two variables to lookup data in a table. When I need to do this I add another column to my data table (inserted on the left). In that column I concatenate the two variables I will be using (date and price in this case). I can then specify the two variables in my lookup formula. Something like:
=VLOOKUP(A1&B1,'Sheet2'!A1:E4000,5,0)
Hope this helps. If I'm not clear let me know where you need clarification.
Barrie
Posted by mo on May 17, 2001 1:25 PM
Hi alladin, a while ago you posted three solutions to my problem, you and Dave suggested pivot tables. That was fine it worked well, but then I wanted to make it a shared workbook, and thus cant use pivot tables.
The third solution you provided was really helpful.
Thankyou very much
Posted by DS1 on May 17, 2001 1:42 PM
miscalculating of lookup function
i used the lookup wizard,and it worked fairly well, but i still have some problems
my lookup prices and dates are in increments of 3, and in the lookup wizard, when you tell it which row(column) to look at, i used the option Not an exact match- then entered what value to look at, it gives the message that it will find a value in the row(column) which is less than or equal to my input. Of those cells who's value is exactly equal to the row headings, the value found is wrong,
for example if $3,(date(150)), = 44.3 in the chart, but actually it gives the value for
$0, (date(150)) =44.1
It uses the value that is closest, but less than the value, not equal to, when it actually is equal to.. let me know if you can help.. thanks again.
Posted by DS1 on May 17, 2001 1:42 PM
miscalculating of lookup function
i used the lookup wizard,and it worked fairly well, but i still have some problems
my lookup prices and dates are in increments of 3, and in the lookup wizard, when you tell it which row(column) to look at, i used the option Not an exact match- then entered what value to look at, it gives the message that it will find a value in the row(column) which is less than or equal to my input. Of those cells who's value is exactly equal to the row headings, the value found is wrong,
for example if $3,(date(150)), = 44.3 in the chart, but actually it gives the value for
$0, (date(150)) =44.1
It uses the value that is closest, but less than the value, not equal to, when it actually is equal to.. let me know if you can help.. thanks again.
Posted by IML on May 17, 2001 2:54 PM
Re: miscalculating of lookup function
I may be a little confused...if you don't want to find an exact match from your table data, what do you want to find? Maybe you could provide a small sample of say a 4x4 area including labels. I often use the lookup wizard as a starting point to get a formula, and then replace the "hardcoded" values entered in the formula with cell addresses.
Posted by DS1 on May 18, 2001 5:38 AM
Re: miscalculating of lookup function
I tried replacing the given formula from the wizard, but it gives me a #n/a error. when i am going through the wizard, it asks me to enter the row of which to find the data, and when i am in the cell which matches a value in the chart exactly, i find that value and click, then it tells me that "excel has found an error in the cell in your table, please exit and fix. but i can't see anything wrong with it, so then i pasted the entire table, values only and referenced that and it still didn't work
sample
100 104 108 112
$10 1203 1290 1340 1360
$13 1400 1501 1522 1630
$16 1680 1690 1702 1720
$19 1801 1820 1890 1920
if the two values i am looking for are:
$11,102, it returns 1203, which is fine, because i specify that there is no exact match in the table, so it finds whatever is lesser or equal to. but if i am looking for $16, $103 (because of the 16, even though i click 16 in the list of rows to match this number to, it returns 1400, i have tried putting in the TRUE at the end, but it still didn't work. Here is my smpl formula
=VLOOKUP(E15,Sheet1!$C$10:$R$177, MATCH(G15,Sheet1!$C$9:$R$9,))
where E15 is my price, c10:r177 is my table including the row and column headings, g15 is my time(which i have rounded down so that there is an exact match in the table for every time value), and c9:r9 is the time ranges(column headings across top)
hope this helps, thanks again...
Posted by DS1 on May 18, 2001 5:38 AM
Re: miscalculating of lookup function
I tried replacing the given formula from the wizard, but it gives me a #n/a error. when i am going through the wizard, it asks me to enter the row of which to find the data, and when i am in the cell which matches a value in the chart exactly, i find that value and click, then it tells me that "excel has found an error in the cell in your table, please exit and fix. but i can't see anything wrong with it, so then i pasted the entire table, values only and referenced that and it still didn't work
sample
100 104 108 112
$10 1203 1290 1340 1360
$13 1400 1501 1522 1630
$16 1680 1690 1702 1720
$19 1801 1820 1890 1920
if the two values i am looking for are:
$11,102, it returns 1203, which is fine, because i specify that there is no exact match in the table, so it finds whatever is lesser or equal to. but if i am looking for $16, $103 (because of the 16, even though i click 16 in the list of rows to match this number to, it returns 1400, i have tried putting in the TRUE at the end, but it still didn't work. Here is my smpl formula
=VLOOKUP(E15,Sheet1!$C$10:$R$177, MATCH(G15,Sheet1!$C$9:$R$9,))
where E15 is my price, c10:r177 is my table including the row and column headings, g15 is my time(which i have rounded down so that there is an exact match in the table for every time value), and c9:r9 is the time ranges(column headings across top)
hope this helps, thanks again...
Posted by Mark W. on May 18, 2001 7:14 AM
Re: miscalculating of lookup function
DS1, this sounds like a data type mismatch!
Are you sure both of your 16's are the same
data type (numeric vs. text) or is there a
difference of precision that is being masked
by a number format -- 16.01 perhaps?
Posted by Mark W. on May 18, 2001 7:14 AM
Re: miscalculating of lookup function
DS1, this sounds like a data type mismatch!
Are you sure both of your 16's are the same
data type (numeric vs. text) or is there a
difference of precision that is being masked
by a number format -- 16.01 perhaps?
Posted by Aladin Akyurek on May 18, 2001 7:41 AM
Also...
Your formula probably miscalculates the column number:
=VLOOKUP(...,MATCH(...)+1)
should be what you need.
Aladin
Posted by Aladin Akyurek on May 18, 2001 7:41 AM
Also...
Your formula probably miscalculates the column number:
=VLOOKUP(...,MATCH(...)+1)
should be what you need.
Aladin
Posted by Aladin Akyurek on May 18, 2001 8:33 AM
Continuing the track
I'll assume that your sample data occupy C9:G13 and consists of:
{"",100,104,108,112; 10,1203,1290,1340,1360; 13,1400,1501,1522,1630; 16,1680,1690,1702,1720;19,1801,1820,1890,1920}
I'll also assume A1=11, B1=102
=VLOOKUP(A1,$C$10:$G$13, MATCH(B1,$D$9:$G$9)+1)
retrieves 1203.
When A1=16 and B1=103, the above formula retrieves 1680.
Aladin
======================================
Posted by Aladin Akyurek on May 18, 2001 8:33 AM
Continuing the track
I'll assume that your sample data occupy C9:G13 and consists of:
{"",100,104,108,112; 10,1203,1290,1340,1360; 13,1400,1501,1522,1630; 16,1680,1690,1702,1720; 19,1801,1820,1890,1920}
I'll also assume A1=11, B1=102
=VLOOKUP(A1,$C$10:$G$13, MATCH(B1,$D$9:$G$9)+1)
retrieves 1203.
When A1=16 and B1=103, the above formula retrieves 1680.
Aladin
======================================
Posted by DS1 on May 18, 2001 8:45 AM
Thank you Mark & others
Thanks, the values are different after 14 0's, So i rounded both the table values and my lookup numbers to 4places.. Thank you sooo much!!!!
Posted by DS1 on May 18, 2001 8:45 AM
Thank you Mark & others
Thanks, the values are different after 14 0's, So i rounded both the table values and my lookup numbers to 4places.. Thank you sooo much!!!!
