find the cell above any number in any range

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

Hi Don,

below is the first part of my formula :

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

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

 

[Guest]

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,B6B6,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

 

[Biff]

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!B6B52=A1,ROW(Sheet1!B6B52)))-2,MAX(IF(Sheet1!B6B52=A1,COLUMN(Sheet1!B6B52)))-1)

Biff

 

[Guest]

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

 

[Guest]

Hi bj,

all i get is your formula has an error, this either on a blank workbook or
my live one and applies to both formulas dc6 / b53 had a mess but can't suss
ir out, any ideas ?


thanks

steve

 

[Guest]

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,B6B6,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

 

[Guest]

Multiple occurances makes it much more complicated. I am starting to think a
macro to do the investigation might be the way to go.

Additional question: If you can have up to 4 occurances of the 7191.
How do you want the results of the cell above to be handled?

Here is a quick brute force macro
This one would put the results of the cell above the 7191 in Sheet 1 column
B for as many occurances as there are.

My computer is acting up and won't let me test the macro so hopefully I
don't have too many typos in it.

sub sa()
fl = 1 'number of matches
for r = 6 to 52 'row
for c = 2 to 80 'column
if Sheets("Sheet2").Cells(r,c)=7191 then
Sheets("Sheet1").Cells(fl,2) =sheets("Sheet2").cells(r-1,c)
fl=fl+1
end if
next c
next r
end sub
if

 

[Biff]

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

 

[Harlan Grove]

Biff wrote...

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.

....

VBA not needed, and merged cells aren't a problem for this. As for
duplicates, either you search first by row then by column or first by
column then by row. For example, searching for 5 in

1 2 3 4
6 7 5 8
9 5 0 1

should the result be 3 (matching the 5 in the 2nd row, 3rd column
first,
so mathcing by row then column) or 7 (matching the 5 in the 2nd column,
3rd row first, so matching by column then row)?

To match by row then column, you could use an array formula like

=CELL("Address",INDEX(Tbl,MATCH(TRUE,COUNTIF(OFFSET(Tbl,
ROW(Tbl)-CELL("Row",Tbl),0,1,),v)>0,0)-1,
MATCH(v,INDEX(Tbl,MATCH(TRUE,COUNTIF(OFFSET(Tbl,
ROW(Tbl)-CELL("Row",Tbl),0,1,),v)>0,0),0),0)))

where Tbl represents the data range and v the value to match.

 

[Biff]

Hi Harlan

Can't get that to work.

This portion (both instances):

COUNTIF(OFFSET(tbl,ROW(tbl)-CELL("Row",tbl),0,1),A15)

Evaluates to:

COUNTIF({#VALUE!,#VALUE!,#VALUE!},A15)

So the final result of the formula is #N/A.

Biff

 

[Biff]

Disregard!

I got it working.

Biff

Hi Harlan

Can't get that to work.

This portion (both instances):

COUNTIF(OFFSET(tbl,ROW(tbl)-CELL("Row",tbl),0,1),A15)

Evaluates to:

COUNTIF({#VALUE!,#VALUE!,#VALUE!},A15)

So the final result of the formula is #N/A.

Biff