Hi!

Well, you didn't mention that the range had merged cells(they usually cause
nothing but grief, as you're finding out)

I also see in your reply to BJ that you may have multiple occurances.

So, your options are extremely limited. Maybe BJ's macro will solve your
problem.

Biff

"steve alcock" wrote in message
...
Hi Biff,

I copied and pasted your formula in to a new workbook ( having tried in my
live one ) and setup the sheet but ( as did on my live workbook) I get a
message after CTRL+SHIFT+ENTER " array formulas are not valid in merged
cells
" any ideas why please ?

regards

steve

"Biff" wrote:

Hi!

Any possibility of duplicate 7191's being in the range?

Not reliable if there are dupes.

Lookup value in Sheet2 A1 = 7191

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=OFFSET(Sheet1!A1,MAX(IF(Sheet1!B6:DB52=A1,ROW(She et1!B6:DB52)))-2,MAX(IF(Sheet1!B6:DB52=A1,COLUMN(Sheet1!B6:DB52)) )-1)

Biff

"steve alcock" wrote in message
...
Hi Don,

below is the first part of my formula :

=IF(MATCH(B3,bookings!$B$7:$DB$55)=B3,"true","fals e")

and returns me #N/A

I think the problem is :

I have blank / text and formaula cells throughout the range b7:db55

any ideas please ?

thanks

steve


"Don Guillett" wrote:

Use MATCH to find the row and then -1 and INDEX the column match
row,1

--
Don Guillett
SalesAid Software

"steve alcock" wrote in
message
...
Hi team,

need big help here :

I want to be able to lookup from one worksheet with a value of 7191
on
sheet2
b6:db52, match the value ( 7191 ) then return me the cell
information
directly above the matched cell, anyone any ideas please

thanks

steve