Solved: Showing all offset matches on different sheet using formula?

[Simon Lloyd]

Hi all, i have a list of names on sheet Names & Areas column A starting at A3 and in column B next to each name is either the word "Used" or "Unused", on sheet called Crewing S21 i have the word "Unused" i would like to have some cells with formula in so that if the word in S21 matches any in Names & Areas column B to show the offset -1 column name.

I have tried this
=IF(OFFSET('Names & Areas'!$B$3,0,MATCH(Crewing!$S$21,'Names & Areas'!$B$3:$B$150,1)*1,46,1),OFFSET('Names & Areas'!B3,0,-1,46,1),"")but it doesnt show the name, and i have tried this
=IF((OFFSET('Names & Areas'!$B$3,0,MATCH(Crewing!$S$21,'Names & Areas'!$B$3:$B$150,0)-1,46,1)<>"Used"),"",OFFSET('Names & Areas'!B3,0,0,46,1))which will display used but i cant get any to work, i want to be able to copy the formula down for around 30 rows but there wouldn't be more than that "UnUsed" offsets to display!

Any ideas?

Regards,
SImon

[Simon Lloyd]

Hi, This works as an array formula but it seems only for Names & Areas B3
{=IF((OFFSET('Names & Areas'!$B$3,0,MATCH(Crewing!$S$21,Utilized,1)*1,46,1)<>"Used"),OFFSET('Names & Areas'!$B$3,0,-1,46,1),"")}if i copy the formula down i get all the same result!

Regards,
Simon

P.S the range "Utilized" = Sheets("Names & Areas").Range("B3:B150")

[Bob Phillips]

=IF(ISERROR(SMALL(IF(Utilized=$S$21,ROW('Names & Areas'!$A$3:$A$150)-MIN(ROW('Names & Areas'!$A$3:$A$150))+1,""),ROW(A1))),"",
INDEX('Names & Areas'!$A$3:$A$150,SMALL(IF(Utilized=$S$21,ROW('Names & Areas'!$A$3:$A$150)-MIN(ROW('Names & Areas'!$A$3:$A$150))+1,""),ROW(A1))))

[Simon Lloyd]

Thanks Bob!, well i doubt i would have managed soemthing that complicated - i forgot about the -MIN where you removce all other results from the array, you explained that the other day, guess i didn't really take it in!

I'm at home now (some 19hrs later!) and dog tired so going to bed will use your formula later - btw is it an array formula?, for some reason the formula i posted gave two seperate results depending on whether i made it an array formula or simply just pressed enter - as an array it produced the result of the offset to B3 as needed - but as a normal formula it displayed the offset of Crewing!S21 from the sheet Names & Areas!B21 - Odd! (or well i thought it was!).

Tired regards,
Simon

[Bob Phillips]

Simon,

Yes it is an array formula, as soon as I include a formula with ROW(rng) it in it is a good indication that it ia an array formula.

The reason you sometimes see different results for array entered and no-array entered formulae is because array entering it will return the correct item from with the array of results, and not array entering it returns the FIRST item in that array of results. Not always of course, often non-array entering just returns #VALUE, but it does happen sometimes. That is the worst case IMO, I have been caught a few times wondering why I am getting the wrong answer, and it was just because it was not array entered. No such obfuscation with #VALUE.

[Simon Lloyd]

Thanks for the explanation Bob!, i guess you are on "O" of the "Words To Get In Daily" dictionary

No such obfuscation with #VALUE.

When filtering using a criteria, ostensibily

obviating the need for DATEVALUE......I have no idea what those worsd mean but was too pig headed to ask! LOL

Regards,
Simon

[Simon Lloyd]

Bob, i have just entered the array formula in Crewing!S22 and it didnt work, the word to look for is in Crewing!S21, if the word is found in Utilized (thats Names & Crewing!B3:B150) then display the offset(0,-1) which will be a name in column A starting at A3:A150, the result at the moment is "", i will fiddle with it when i have a little time later tonight1

regards,
Simon

[Bob Phillips]

LOL! I didn't notice that.

But honestly, I use those words regulalrly.

But I also admit to getting an RSS feed from a Word of The Day site (can you believe that they gave 'genial' the other day).

[Bob Phillips]

Just tried it again Simon, and it works here.

[Simon Lloyd]

Bob attached is the workbook, i suppose i should have mentioned that the Used or Unused is created by this formula
=IF(COUNTIF(Crewing!$B$1:$Q$33,A3)<1,"Unused","Used")

Regards,
Simon

[Bob Phillips]

Simon,

The named range 'Utilized' defines B3:B105, NOT B3:B150. Therefore all ranges in the formula must also refer to 105 not 150. These ranges have to the same size to work properly.

I'm to bed now, so I will check again in the morning.

Bob

[Simon Lloyd]

oooops! big typo!, sorry Bob will look at that thanks, don't be dreaming of any more "O" words......Next alphabetical letter will surfice!

Regards,
Simon

[Simon Lloyd]

Changes made works as you said in the first place!,

Regards,
SImon

[Simon Lloyd]

Thanks for the solution Bob, as a follow up could you - if you have time - explain what was wrong with my formula which displayed the offset value of B3?



{=IF((OFFSET('Names & Areas'!$B$3,0,MATCH(Crewing!$S$21,Utilized,1)*1,46,1)<>"Used"),OFFSET('Name s & Areas'!$B$3,0,-1,46,1),"")}
Regards,
Simon