It would be easier to understand with a concrete example
in english, but I'll take a guess - I think you're trying
to look up a concatenation of 2 cells (i8 & bm1) on
another list, but you want to look up the individual
pieces of the concatenation on a separate array.
for ex:
look up "a"[i8] concatenated with "b"[bm1], on 2 arrays,
one of which might contain "a" and another of which might
contain "b". A third array will contain either "yes"
or "no". only use reference of match if 3rd array
contains "no"
Then you want to offset from a given cell reference by the
returned match number.
Does this sound right?
I'm not sure if the match can work with the & as you were
using it, but I'd add one more column to your data, which
concatenates all 3 arrays into one.
....
I put these into a1 thru E7:
array 1 array 2 array 3 concatenate start
other other no otherotherno 1 away
look meup Yes lookmeupYes 2 away
other other Yes otherotherYes 3 away
look meup no lookmeupno 4 away
other other Yes otherotherYes 5 away
other other no otherotherno 6 away
=OFFSET(E1,MATCH(M15&M16&"no",$D$2:$D$7,0),0,1,1)
this formula results in offseting from "start" by 4, which
is the row number where "lookmeupno" is found.
hth,
Dave
then this formula will find location(row # in this case)
of match only when there is a no in the same row:
-----Original Message-----
I'm trying to write a formula using OFFSET & MATCH to
fill in another cell.
What I want to do is OFFSET I19, MATCH I8&BM1 to cells
I19:I445&AK19:AK445
but if the adjacent cell in column BC says "Yes" don't
use it and skip to the
next match (there will be another cell that matches
without Yes). I came up
with this formula but it doesn't work, the result is
blank.
Quoted = BC1:BC445
Array Entered:
=IF(ISNA(OFFSET($I$19,MATCH
($I8&BM$1,$I$19:$I$445&$AK$19:$AK$445&Quoted<"Yes ",0)-
1,18,-1,-1)),0,OFFSET($I$19,MATCH
($I8&BM$1,$I$19:$I$445&$AK$19:$AK$445&Quoted<"Yes ",0)-
1,18,-1,-1))
If I take out the Quoted<"Yes" it finds the first match
but sometimes cell
BCxx says Yes and that's the wrong result.
I hope I wasn't too confussing.
Thanks for your help
Joe
.
|