Formula Help With MATCH & OFFSET

[Guest]

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

 

[dave]

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