Row position of specified column and value

[engineer]

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

 

[Don Guillett]

Look in the help index for MATCH

 

[engineer]

The Match function requires you to specify a range. I am only wanting to
specify the column number.

 

[Max]

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]

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

 

[T. Valko]

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?

 

[engineer]

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]

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)

 

[Max]

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.

 

[Don Guillett]

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)&""))

 

[engineer]

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.

 

[T. Valko]

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.

 

[Max]

It's all a matter of making it relative ...

Assume the source data is within say: B710
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 here:
http://cjoint.com/?myahwyzy6w

Voila? celebrato, hit the YES below