Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup returning multiple (incorrect) values
I started with the code from he
http://office.microsoft.com/en-us/ex...260381033.aspx My current formula is: =INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$ 30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2) I don't fully understand how the row function works, specifically how the ROW(1:1) statement plays into the SMALL function or even what ROW(1:1) signifies, and because of this I can't figure out why the whole formula is basically doing things right but instead of identifying occurrences of A$2 and spitting out that data, it's identifying occurrences of A$2 and spitting out the data of the next line below. Any help would be greatly appreciated. Ryan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup returning multiple (incorrect) values
=INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$ 30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2)
Let's break this formula down. INDEX looks for the value(s) in the first group: tests!$C$2:$G$30000 within the columns listed in the 2nd group: SMALL(IF(tests!$C$2:$C$30000=A$2,ROW(tests!$C$2:$G $30000)),ROW(1:1)) and the rows in the 3rd group: 2 Now, the columns pieces is most difficult so: SMALL takes the minimum value of the list... If the values in tests!$C$2:$C$30000 equal the value in A2 THEN Use row of tests!$C$2:$G$30000, or, 2 ELSE Use Row of 1:1, or 1. So, it sounds like you are finding situations where tests!$C$2:$C$30000 does not equal A2, and thus you are getting Row 1 as your row value in Index, and since your lookup table starts in row 2, you are getting the value above the one you want. Course, a little help would be to know what you are tryign to accomplish... "seed" wrote: I started with the code from he http://office.microsoft.com/en-us/ex...260381033.aspx My current formula is: =INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$ 30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2) I don't fully understand how the row function works, specifically how the ROW(1:1) statement plays into the SMALL function or even what ROW(1:1) signifies, and because of this I can't figure out why the whole formula is basically doing things right but instead of identifying occurrences of A$2 and spitting out that data, it's identifying occurrences of A$2 and spitting out the data of the next line below. Any help would be greatly appreciated. Ryan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup returning multiple (incorrect) values
I have a sheet of information for tests ('tests' sheet, obviously), with all
data for each test arranged into rows, that my lab performs with the date in column C, test type in column D, # of samples in E, etc. A separate sheet ('date'), the one using the formula, is a nicely formatted sheet that (this is the attempt) sorts the information for all tests on each separate day. The date is located, for this day at least, in A2. What I'm trying to do is pull all of the information from 'tests' column C,D,E, and F that coincides with multiple occurrences of the value given in 'date' A2. What this formula is giving me is the row below the row that I want. Seems like kind of a pain just to make a nicely formatted daily schedule, but from there I can use the 'date' info to automatically fill in all of the data that is normally entered manually into our scheduling sheet. SO! It looks like simply changing the range to beginning at C1 rather than C2 has fixed the problem, though I still can't say that I completely understand why. Thank you very much Sean. "Sean Timmons" wrote: =INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$ 30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2) Let's break this formula down. INDEX looks for the value(s) in the first group: tests!$C$2:$G$30000 within the columns listed in the 2nd group: SMALL(IF(tests!$C$2:$C$30000=A$2,ROW(tests!$C$2:$G $30000)),ROW(1:1)) and the rows in the 3rd group: 2 Now, the columns pieces is most difficult so: SMALL takes the minimum value of the list... If the values in tests!$C$2:$C$30000 equal the value in A2 THEN Use row of tests!$C$2:$G$30000, or, 2 ELSE Use Row of 1:1, or 1. So, it sounds like you are finding situations where tests!$C$2:$C$30000 does not equal A2, and thus you are getting Row 1 as your row value in Index, and since your lookup table starts in row 2, you are getting the value above the one you want. Course, a little help would be to know what you are tryign to accomplish... "seed" wrote: I started with the code from he http://office.microsoft.com/en-us/ex...260381033.aspx My current formula is: =INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$ 30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2) I don't fully understand how the row function works, specifically how the ROW(1:1) statement plays into the SMALL function or even what ROW(1:1) signifies, and because of this I can't figure out why the whole formula is basically doing things right but instead of identifying occurrences of A$2 and spitting out that data, it's identifying occurrences of A$2 and spitting out the data of the next line below. Any help would be greatly appreciated. Ryan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup returning multiple (incorrect) values
Sometimes it's easier to just accept that things work. :-)
You can always click the fsubx next to your formula bar and click your way through the formula. That's IF you want to know the answer. "seed" wrote: I have a sheet of information for tests ('tests' sheet, obviously), with all data for each test arranged into rows, that my lab performs with the date in column C, test type in column D, # of samples in E, etc. A separate sheet ('date'), the one using the formula, is a nicely formatted sheet that (this is the attempt) sorts the information for all tests on each separate day. The date is located, for this day at least, in A2. What I'm trying to do is pull all of the information from 'tests' column C,D,E, and F that coincides with multiple occurrences of the value given in 'date' A2. What this formula is giving me is the row below the row that I want. Seems like kind of a pain just to make a nicely formatted daily schedule, but from there I can use the 'date' info to automatically fill in all of the data that is normally entered manually into our scheduling sheet. SO! It looks like simply changing the range to beginning at C1 rather than C2 has fixed the problem, though I still can't say that I completely understand why. Thank you very much Sean. "Sean Timmons" wrote: =INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$ 30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2) Let's break this formula down. INDEX looks for the value(s) in the first group: tests!$C$2:$G$30000 within the columns listed in the 2nd group: SMALL(IF(tests!$C$2:$C$30000=A$2,ROW(tests!$C$2:$G $30000)),ROW(1:1)) and the rows in the 3rd group: 2 Now, the columns pieces is most difficult so: SMALL takes the minimum value of the list... If the values in tests!$C$2:$C$30000 equal the value in A2 THEN Use row of tests!$C$2:$G$30000, or, 2 ELSE Use Row of 1:1, or 1. So, it sounds like you are finding situations where tests!$C$2:$C$30000 does not equal A2, and thus you are getting Row 1 as your row value in Index, and since your lookup table starts in row 2, you are getting the value above the one you want. Course, a little help would be to know what you are tryign to accomplish... "seed" wrote: I started with the code from he http://office.microsoft.com/en-us/ex...260381033.aspx My current formula is: =INDEX(tests!$C$2:$G$30000,SMALL(IF(tests!$C$2:$C$ 30000=A$2,ROW(tests!$C$2:$G$30000)),ROW(1:1)),2) I don't fully understand how the row function works, specifically how the ROW(1:1) statement plays into the SMALL function or even what ROW(1:1) signifies, and because of this I can't figure out why the whole formula is basically doing things right but instead of identifying occurrences of A$2 and spitting out that data, it's identifying occurrences of A$2 and spitting out the data of the next line below. Any help would be greatly appreciated. Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup returning incorrect value | Excel Discussion (Misc queries) | |||
Lookup returning incorrect result | Excel Worksheet Functions | |||
lookup returning incorrect cell values | Excel Worksheet Functions | |||
Returning multiple corresponding values using lookup in a list | Excel Discussion (Misc queries) | |||
Returning all values from a lookup - not just the first/last one | Excel Worksheet Functions |