find the cell above any number in any range
 

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

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

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

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

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

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

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


"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

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

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


"steve alcock" wrote:

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

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

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(OFFSE T(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.

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

"Harlan Grove" wrote in message
ups.com...
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(OFFSE T(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.

Disregard!

I got it working.

Biff

"Biff" wrote in message
...
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

"Harlan Grove" wrote in message
ups.com...
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(OFFSE T(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.