Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
I would like to know the row position of a specified column and value in a
table. If multiple occurences of the specified value occur in a particular column, I want the bottom most row position returned. For example, in the folowing table, the function would specify column 2 (or B), value of 6, and return 3 since the bottom most 6 in column 2 is row 3. A B C 1 5 8 2 6 9 3 6 10 4 7 11 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
Look in the help index for MATCH
-- Don Guillett Microsoft MVP Excel SalesAid Software "engineer" wrote in message ... I would like to know the row position of a specified column and value in a table. If multiple occurences of the specified value occur in a particular column, I want the bottom most row position returned. For example, in the folowing table, the function would specify column 2 (or B), value of 6, and return 3 since the bottom most 6 in column 2 is row 3. A B C 1 5 8 2 6 9 3 6 10 4 7 11 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
The Match function requires you to specify a range. I am only wanting to
specify the column number. "Don Guillett" wrote: Look in the help index for MATCH -- Don Guillett Microsoft MVP Excel SalesAid Software "engineer" wrote in message ... I would like to know the row position of a specified column and value in a table. If multiple occurences of the specified value occur in a particular column, I want the bottom most row position returned. For example, in the folowing table, the function would specify column 2 (or B), value of 6, and return 3 since the bottom most 6 in column 2 is row 3. A B C 1 5 8 2 6 9 3 6 10 4 7 11 . |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
In E1: 2 (input the col number, eg col B = 2)
In E2: 6 (input the value in col B to be found) Then in E3, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =MAX(IF(OFFSET(A1:A4,,E1-1)=E2,ROW(A1:A4))) Success? celebrate it, hit the YES below -- Max Singapore --- "engineer" wrote: I would like to know the row position of a specified column and value in a table. If multiple occurences of the specified value occur in a particular column, I want the bottom most row position returned. For example, in the folowing table, the function would specify column 2 (or B), value of 6, and return 3 since the bottom most 6 in column 2 is row 3. A B C 1 5 8 2 6 9 3 6 10 4 7 11 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
As posted in the other branch, one way to engineer it ...
---------------- In E1: 2 (input the col number, eg col B = 2) In E2: 6 (input the value in col B to be found) Then in E3, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =MAX(IF(OFFSET(A1:A4,,E1-1)=E2,ROW(A1:A4))) Success? celebrate it, hit the YES below -- Max Singapore --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
Do you want the relative row number or the absolute row number?
Let's assume your sample data is in the range A10:C13. What result do you expect, 3 or 12? -- Biff Microsoft Excel MVP "engineer" wrote in message ... I would like to know the row position of a specified column and value in a table. If multiple occurences of the specified value occur in a particular column, I want the bottom most row position returned. For example, in the folowing table, the function would specify column 2 (or B), value of 6, and return 3 since the bottom most 6 in column 2 is row 3. A B C 1 5 8 2 6 9 3 6 10 4 7 11 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
I expect the result 3. Hence Max' formula didn't accomplish what I needed it
to, although close. Can you get me the answer of 3? Thanks, "T. Valko" wrote: Do you want the relative row number or the absolute row number? Let's assume your sample data is in the range A10:C13. What result do you expect, 3 or 12? -- Biff Microsoft Excel MVP "engineer" wrote in message ... I would like to know the row position of a specified column and value in a table. If multiple occurences of the specified value occur in a particular column, I want the bottom most row position returned. For example, in the folowing table, the function would specify column 2 (or B), value of 6, and return 3 since the bottom most 6 in column 2 is row 3. A B C 1 5 8 2 6 9 3 6 10 4 7 11 . |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
Try this...
Assume the data is in the range A2:C5 E2 = column number = 2 F2 = lookup number = 6 =LOOKUP(2,1/(INDEX(A2:C5,,E2)=F2),ROW(A2:C5)-MIN(ROW(A2:C5))+1) -- Biff Microsoft Excel MVP "engineer" wrote in message ... I expect the result 3. Hence Max' formula didn't accomplish what I needed it to, although close. Can you get me the answer of 3? Thanks, "T. Valko" wrote: Do you want the relative row number or the absolute row number? Let's assume your sample data is in the range A10:C13. What result do you expect, 3 or 12? -- Biff Microsoft Excel MVP "engineer" wrote in message ... I would like to know the row position of a specified column and value in a table. If multiple occurences of the specified value occur in a particular column, I want the bottom most row position returned. For example, in the folowing table, the function would specify column 2 (or B), value of 6, and return 3 since the bottom most 6 in column 2 is row 3. A B C 1 5 8 2 6 9 3 6 10 4 7 11 . |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
"engineer" wrote:
I expect the result 3. Hence Max' formula didn't accomplish what I needed it to .. The amazing thing is that it DOES return the correct value 3. I suspect that you did not/correctly array-enter the formula as specified in my response. If you didn't array-enter the formula, then it returns an incorrect result of 1. Here's living proof of the expression working fine: http://cjoint.com/?mxl3f8gzei Notice the curly braces { } around the array formula in the formula bar. These are inserted by Excel when you do the array-enter correctly. If you don't see these curlies, then it wasn't done correctly, and you need to click inside the formula bar and re-do the CSE. Recheck visually again. This is the ONLY way to confirm that its correctly array-entered. Success at long last? Celebrate it, whack the YES below. -- Max Singapore --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
Max,
It does not work. I think the confusion may lie in the fact that "A" is the value in Cell "A1", "B" is the value in Cell "B1", etc. You've assumed my column titles are the Excel column files. It probably would have been clearer if I used numbers or "X", "Y", "Z" as my column titles. So maybe I didn't pose the question clear enough. Valko is correct, in that I am wanting the row number even if the table is shifted down so many rows. So three would still be the answer no matter how far the table is shifted down. "Max" wrote: "engineer" wrote: I expect the result 3. Hence Max' formula didn't accomplish what I needed it to .. The amazing thing is that it DOES return the correct value 3. I suspect that you did not/correctly array-enter the formula as specified in my response. If you didn't array-enter the formula, then it returns an incorrect result of 1. Here's living proof of the expression working fine: http://cjoint.com/?mxl3f8gzei Notice the curly braces { } around the array formula in the formula bar. These are inserted by Excel when you do the array-enter correctly. If you don't see these curlies, then it wasn't done correctly, and you need to click inside the formula bar and re-do the CSE. Recheck visually again. This is the ONLY way to confirm that its correctly array-entered. Success at long last? Celebrate it, whack the YES below. -- Max Singapore --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
You've assumed my column titles are the Excel column files
I did that also. So, we'll need to tweak the formula to find the correct column. E2 = column to look in based on the column header = B F2 = lookup number = 6 =LOOKUP(2,1/(INDEX(A2:C5,,MATCH(E2,A1:C1,0))=F2),ROW(A2:C5)-MIN(ROW(A2:C5))+1) Assuming the column headers are in the range A1:C1. Numeric data in the range A2:C5. -- Biff Microsoft Excel MVP "engineer" wrote in message ... Max, It does not work. I think the confusion may lie in the fact that "A" is the value in Cell "A1", "B" is the value in Cell "B1", etc. You've assumed my column titles are the Excel column files. It probably would have been clearer if I used numbers or "X", "Y", "Z" as my column titles. So maybe I didn't pose the question clear enough. Valko is correct, in that I am wanting the row number even if the table is shifted down so many rows. So three would still be the answer no matter how far the table is shifted down. "Max" wrote: "engineer" wrote: I expect the result 3. Hence Max' formula didn't accomplish what I needed it to .. The amazing thing is that it DOES return the correct value 3. I suspect that you did not/correctly array-enter the formula as specified in my response. If you didn't array-enter the formula, then it returns an incorrect result of 1. Here's living proof of the expression working fine: http://cjoint.com/?mxl3f8gzei Notice the curly braces { } around the array formula in the formula bar. These are inserted by Excel when you do the array-enter correctly. If you don't see these curlies, then it wasn't done correctly, and you need to click inside the formula bar and re-do the CSE. Recheck visually again. This is the ONLY way to confirm that its correctly array-entered. Success at long last? Celebrate it, whack the YES below. -- Max Singapore --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Row position of specified column and value
It's all a matter of making it relative ...
Assume the source data is within say: B7:D10 In F7: 2 (input the col number, eg 2nd col = 2) In F8: 6 (input the value in 2nd col to be found) Then in F9, array-entered, ie press CTRL+SHIFT+ENTER to confirm the formula: =MAX(IF(OFFSET(B7:B10,,F7-1)=F8,ROW(1:4))) Key adaptation points to note: B7:B10 = the leftmost col range of the source data 4 = number of rows in the source data A working example of the above is he http://cjoint.com/?myahwyzy6w Voila? celebrato, hit the YES below -- Max Singapore --- "engineer" wrote: Max, It does not work. I think the confusion may lie in the fact that "A" is the value in Cell "A1", "B" is the value in Cell "B1", etc. You've assumed my column titles are the Excel column files. It probably would have been clearer if I used numbers or "X", "Y", "Z" as my column titles. So maybe I didn't pose the question clear enough. Valko is correct, in that I am wanting the row number even if the table is shifted down so many rows. So three would still be the answer no matter how far the table is shifted down. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find value in column, return value in same position of other colum | Excel Worksheet Functions | |||
How insert a dash in the third position of a column with data? | Excel Discussion (Misc queries) | |||
function for Column Position | Excel Discussion (Misc queries) | |||
Finding Column Position | Excel Discussion (Misc queries) | |||
Select player from list and get his position in next column | Excel Discussion (Misc queries) |