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

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

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



.

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

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
---

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

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




.

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




.

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
---

Row position of specified column and value
 

I still don't understand why match won't get what you want??
=MATCH(6,F:F)
or if 2 (col B) in e1 and 6 in e2 and range sorted. Others better but..
=MATCH(E2,INDIRECT(""&LEFT(ADDRESS(1,E1,4),FIND(1, ADDRESS(1,E1,4))-1)&":"&LEFT(ADDRESS(1,E1,4),FIND(1,ADDRESS(1,E1,4) )-1)&""))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"engineer" wrote in message
...
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



.

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
---

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
---

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.