Hi bj,

having investigated a bit more ( it is still early yet :-) ) :

by altering your formula to :
b56
=IF(ISERROR(MATCH(7191,B6:B55,0)),1)
dc6
=IF(ISERROR(MATCH(7191,B6:DB6,0)),1)

7191 is in locations x,z,ab,ad 7

dc7 = false

x,z,ab,ad 56 all = false

sheet1 ( again altered your formula )

=OFFSET(Sheet2!B6,MATCH(A1,Sheet2!DC6:Sheet2!DC55, 0)-2,MATCH(A1,Sheet2!B55:Sheet2!DC55,0)-1)

a1 = 7191

returns #N/A

even if I make 7191 unique to 1 cell I get the same return #N/A
( I need 7191 to be a max of 4 occrances to 1 min, if not exist then return
me blank )

does this make sense to you ? me Im lost before I start here hence my call
for help.

I do appreciate the time you are taking, thanks

steve





"bj" wrote:

I have seen some nice ways to match a value in a block, but for the life of
me, I cannot remember what they are. A workaround while waiting for someone
to com up wtih the nice formula would be to in sheet 2
cells dc6 put
= if(iserror(match(7191,B6:DB6,0),0,1)
paste down to dc52
in cell b53
=if(iserror(match(7191,B6:B62,0),0,1)
and paste to BD53
then on sheet one
in cell of interest
= offset(Sheet2!B6,match(1,Sheet21dc6:dc52,0)-2,match(1,Sheet21B53:dc53,0)-1

"steve alcock" wrote:

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