I have a large table, 5 columns by about 1200 rows, populated with sequential
numbers. the numbers are ordered smallest to largest, left to right and top
to bottom. For example:


M10:Q10 would hold 1,2,3,4, and 5
M11:Q11 would hold 6, 7, 8, 9 and 10
and so on. The numbers do, sometimes, repeat but they stay in order.

I would like to do some sort of look up to find a number in this table and
return the value in a cell 6 cells to the left (or right if it's easier) of
it. If it is a number that repeats, I would like to use the last instnace of
it. I've been fighting this one for a while. Thanks in advance for any help!

With
A1: (the number to find)

This formula (broken into segments for readability) returns the value
that is 6 cells to the right of the last occurrence of that value :
B1: =IF(COUNTIF(M10:Q1200,A1),
INDEX($1:$65536,MAX(INDEX((M10:Q1200=A1)*ROW(M10:Q 1200),0)),
MAX(INDEX((M10:Q1200=A1)*COLUMN(M10:Q1200),0))+6), "n/a")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Mac-OTR" wrote in message
...
I have a large table, 5 columns by about 1200 rows, populated with
sequential
numbers. the numbers are ordered smallest to largest, left to right and
top
to bottom. For example:


M10:Q10 would hold 1,2,3,4, and 5
M11:Q11 would hold 6, 7, 8, 9 and 10
and so on. The numbers do, sometimes, repeat but they stay in order.

I would like to do some sort of look up to find a number in this table and
return the value in a cell 6 cells to the left (or right if it's easier)
of
it. If it is a number that repeats, I would like to use the last instnace
of
it. I've been fighting this one for a while. Thanks in advance for any
help!

It will take me a while to digest it but I will give it a try and let you know.

Thanks so much,

Matt

"Ron Coderre" wrote:

With
A1: (the number to find)

This formula (broken into segments for readability) returns the value
that is 6 cells to the right of the last occurrence of that value :
B1: =IF(COUNTIF(M10:Q1200,A1),
INDEX($1:$65536,MAX(INDEX((M10:Q1200=A1)*ROW(M10:Q 1200),0)),
MAX(INDEX((M10:Q1200=A1)*COLUMN(M10:Q1200),0))+6), "n/a")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Mac-OTR" wrote in message
...
I have a large table, 5 columns by about 1200 rows, populated with
sequential
numbers. the numbers are ordered smallest to largest, left to right and
top
to bottom. For example:


M10:Q10 would hold 1,2,3,4, and 5
M11:Q11 would hold 6, 7, 8, 9 and 10
and so on. The numbers do, sometimes, repeat but they stay in order.

I would like to do some sort of look up to find a number in this table and
return the value in a cell 6 cells to the left (or right if it's easier)
of
it. If it is a number that repeats, I would like to use the last instnace
of
it. I've been fighting this one for a while. Thanks in advance for any
help!

This array formula finds the row of the
last occurrence of Target in MyArray
=MAX((MyArray=Target)*ROW(MyArray))

I will probably spend the next week trying to understand it but it works
perfectly.

Thanks again.

"Mac-OTR" wrote:

It will take me a while to digest it but I will give it a try and let you know.

Thanks so much,

Matt

"Ron Coderre" wrote:

With
A1: (the number to find)

This formula (broken into segments for readability) returns the value
that is 6 cells to the right of the last occurrence of that value :
B1: =IF(COUNTIF(M10:Q1200,A1),
INDEX($1:$65536,MAX(INDEX((M10:Q1200=A1)*ROW(M10:Q 1200),0)),
MAX(INDEX((M10:Q1200=A1)*COLUMN(M10:Q1200),0))+6), "n/a")

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"Mac-OTR" wrote in message
...
I have a large table, 5 columns by about 1200 rows, populated with
sequential
numbers. the numbers are ordered smallest to largest, left to right and
top
to bottom. For example:


M10:Q10 would hold 1,2,3,4, and 5
M11:Q11 would hold 6, 7, 8, 9 and 10
and so on. The numbers do, sometimes, repeat but they stay in order.

I would like to do some sort of look up to find a number in this table and
return the value in a cell 6 cells to the left (or right if it's easier)
of
it. If it is a number that repeats, I would like to use the last instnace
of
it. I've been fighting this one for a while. Thanks in advance for any
help!

Thanks,

I'll try that one too.


"Herbert Seidenberg" wrote:

This array formula finds the row of the
last occurrence of Target in MyArray
=MAX((MyArray=Target)*ROW(MyArray))