Finding a value in a Table

[Mac-OTR]

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!

 

[Ron Coderre]

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:Q1200),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]

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

Thanks so much,

Matt

 

[Herbert Seidenberg]

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

 

[Mac-OTR]

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

Thanks again.

 

[Mac-OTR]

Thanks,

I'll try that one too.