Need help- OFFSET MATCH syntax problem

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi all,
I am trying to return the value of a cell that is
an offset of another cell from a match funtion. I have a
color in cell AY52. I need to find the coresponding color
in a range from BA63:BA165. The range contains spaces and
numbers as well. I then need to return the values in a
range that is offset from that matched color. It begins in
the same row of the match and starts 6 columns to the
right. The range is 5 columns across and 6 rows down. In
other words if the matched color from cell AY52 is in
BA69, the table range is BG69:BK74. I can get the match
value of 13 and I can get to offets from entering it
directly from BA69, but I can't get the combination of the
two to work correctly.

=MATCH(AY52,BA63:BA165,0) <Works, returns 13
=OFFSET(BA69,0,6)<Works returns correct cell value of BG69
=OFFSET(MATCH(AY52,BA63:BA165,0),0,6))) <#value

anyone have 10 seconds to spare to help me out?

Thanks,
Mike
 
One way:

Your table will be returned by

OFFSET(BA63,MATCH(AY52,BA63:BA165,FALSE)-1,6,6,5)
 
offset(RANGE,iRows,iCols,iHeight,iWidth)
Match returns a value not a range => thus your construct wont work


It's not exactly clear what you want..

i assume you want the 13th row, 7th column
in a table that starts in ba63

Note that match returns a 1-based index..
so for the offset you must deduct 1..


Do you need to lookup a value or do you really need a range?

for value lookup
vlookup(ay52,ba63:bg165,7,0)
index(ba63:bg195,match(ay52,ba63:ba165,0),7)

or:

offset(ba63,match(ay52,ba63:ba165,0)-1;6)
returns 1 cell offset 12,6

be aware that
offset(ba63:bb64,match(ay52,ba63:ba165,0)-1;6)
would return a 4 cell range.. shifted 12 down and 6 right

Study Excel help on vlookup, offset,index and match, truly understanding
it will make your life a lot easier.

I'd probably use a simple Vlookup for this.


Capisce <vbg>




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
It still returned a #value error.

-Mike
-----Original Message-----
One way:

Your table will be returned by

OFFSET(BA63,MATCH(AY52,BA63:BA165,FALSE)-1,6,6,5)



.
Click to expand...
 
Sorry, I tried to be clear. Here it is another way. If I
have a color in A1 (lets say cedar), that color has 5
different blends in it. Each blend contains a percentage
of individual unique colors that make up that blend, Id's
by a number. So Cedar blend #1, may contains color #s
555,556 and 702. Blend #2 may have 560,901& 801. I have
a table that has blocks of the main colors, and to the
right begins there are 5 colums that contain the color #s
for each of the 5 blends. So if A1 has Cedar in it, I need
to look for the corresponding Cell in the BA63:BA165 range
the has Cedar. Then I need to go over 6 Columns that
starts the sub-table/range for that color.
So first I need to match a separate cell. Look it up in a
large table, then pull values out a portion that starts 0
rows and 6 columns to the right. I need the values from
that starting cell, 5 columns over and 6 rows down. I have
worked out a very ugly work-a-round via a patchwork of
cells but nothing within a single formula

A BA BG BH BI BJ Bk
Blend Blend Blend Blend
Cedar(A1) 1 2 3 4 5
(BA63)Birchwood 556 350 250 155 500
556 358 720
555



BA69)Autumn Brown


(BA75)Cedar (BG75)800 966 720 22
755 599 566
608
Charcoal
Summary:
Lookup A1, find it in BA63:BA165 range go over to BG? and
pull values for 5 columns over and 6 rows down.
Cedar in this case would range from BK75:BK80 but it would
change for the next color.

Thanks,
Mike.
 
How are you trying to use it?

If you array-enter

=OFFSET(BA63,MATCH(AY52,BA63:BA165,FALSE)-1,6,6,5)

in a 6-row, 5-column range, it will return the values in BG69:BK74.

Or you can sum the table:

=SUM(OFFSET(BA63,MATCH(AY52,BA63:BA165,FALSE)-1,6,6,5))
 
cutting out too much of previous threads doesn't help ;-)

summary:

bg63:bk69 is the first "blendblock"
a1 is the desired blendname
ba63:ba1000 contain the blendnames

match finds cedar in blendnames (as an index)

=Offset(bg63:bk68, match(a1,ba63:ba1000,0)-1,0)
or
=Offset(ba63, match(a1,ba63:ba1000,0)-1,6,6,5)

As these return an ARRAY of 6rowx5col with the selected blend
you'll have to enter an array formula..

copy the formula to a2
select a2:e7 press [F2] and ctrl-shift-enter.

Personally i think the first construct is a bit easier.

hth

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Thanks for the help. Your solution works dandy!
BTW- Its a litte tougher posting via a web browser though
Microsofts NG webpage. I usually use Agent when I'm at
home.

Mike
-----Original Message-----
cutting out too much of previous threads doesn't help ;-)

summary:

bg63:bk69 is the first "blendblock"
a1 is the desired blendname
ba63:ba1000 contain the blendnames

match finds cedar in blendnames (as an index)

=Offset(bg63:bk68, match(a1,ba63:ba1000,0)-1,0)
or
=Offset(ba63, match(a1,ba63:ba1000,0)-1,6,6,5)

As these return an ARRAY of 6rowx5col with the selected blend
you'll have to enter an array formula..

copy the formula to a2
select a2:e7 press [F2] and ctrl-shift-enter.

Personally i think the first construct is a bit easier.

hth

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Mike said:
A BA BG BH BI BJ Bk
Blend Blend Blend Blend
Cedar(A1) 1 2 3 4 5
(BA63)Birchwood 556 350 250 155 500
556 358 720
555



BA69)Autumn Brown


(BA75)Cedar (BG75)800 966 720 22
755 599 566
608
Charcoal
Summary:
Lookup A1, find it in BA63:BA165 range go over to BG? and
pull values for 5 columns over and 6 rows down.
Cedar in this case would range from BK75:BK80 but it would
change for the next color.
Click to expand...
.
Click to expand...
 
Back
Top