Index Match formula error

[jimbogarner]

Hi,

I'm trying to get the following index match formula to work;
=IFERROR(INDEX(Lists!$H$1:$U$100,MATCH(1,(Lists!$H$1:$H$100="*"&$B19&"*")*(Lists!$I$1:$I$100=$E19),0),MATCH(H$16,Lists!$H$1:$U1),0)," ")
This part of the formula is looking up a day (IE Mon), in a list of Mon, Tue, Wed, Thu, Fri.... This is where the errors started to come into the file! However, I need this or something similar in the formula as we have similar groups depending on time slot, however, each day is different... IE 6am-10am is 'Breakfast', however, Mon, Tue & Wed is person A, Thu & Fri is person B...
(Lists!$H$1:$H$100="*"&$B19&"*")
My work security doesn't let me attach the example spreadsheet, however, snip below of the formula and the lists section i'm trying to read from;

Thanks,
James

 

[wjhladik]

=FILTER(LISTS!H1:U100,ISNUMBER(SEARCH(B19,LISTS!H1:H100))*ISNUMBER(SEARCH(E19,LISTS!I1:I100)),"")

or

=IFERROR(INDEX(LISTS!H1:U100,MATCH(1,--ISNUMBER(SEARCH(B19,LISTS!H1:H100))),0)*MATCH(1,--ISNUMBER(SEARCH(E19,LISTS!I1:I100))),0),),"")

Thew first gets all records meeting the conditions and the second gets the first record meeting the condition

 

[jimbogarner]

Thanks, the first formula comes up with NA? the 2nd brings back the entire row but doesn't factor in the Producer 1, producer 2 etc....

IE from the top snip, Monday Breakfast would return Ollie for the Producer 1 section, Will for AP 1, Sim for AP 2 etc. etc.

Thanks,

James

 

[wjhladik]

=FILTER(LISTS!H1:U100,ISNUMBER(SEARCH(B19,LISTS!H1:H100))*ISNUMBER(SEARCH(E19,LISTS!I1:I100)),"")

or

=IFERROR(INDEX(LISTS!H1:U100,MATCH(1,--ISNUMBER(SEARCH(B19,LISTS!H1:H100))),0)*MATCH(1,--ISNUMBER(SEARCH(E19,LISTS!I1:I100))),0),),"")

Thew first gets all records meeting the conditions and the second gets the first record meeting the condition

First one probably means your excel version does not have filter()

I didn't catch you wanted one specific column returned versus the whole thing.

=IFERROR(INDEX(LISTS!H1:U100,MATCH(1,--ISNUMBER(SEARCH(B19,LISTS!H1:H100))),0)*MATCH(1,--ISNUMBER(SEARCH(E19,LISTS!I1:I100))),0),MATCH(H$16,Lists!$H$1:$U1,0)),"")

 

[jimbogarner]

Sorry my fault I should have specified this is in google sheets, not excel. Not sure if that makes any difference?

Weirdly that works for the first line but then doesn't seem to work for any after that?

Thanks,

James

 

[Joe4]

Sorry my fault I should have specified this is in google sheets, not excel. Not sure if that makes any difference?

There are many similarities, but there are differences as well, as they are NOT the same program.
As such, you should always specify in your original posts if you are asking about Google Sheets to avoid any confusion (since we primarily are an Excel forum! )

 

[wjhladik]

Sorry my fault I should have specified this is in google sheets, not excel. Not sure if that makes any difference?

Weirdly that works for the first line but then doesn't seem to work for any after that?
View attachment 93591
View attachment 93592

Thanks,

James

Typo in the formula - missed a $ sign near the end

=IFERROR(INDEX(LISTS!$H$1:$U$100,MATCH(1,--ISNUMBER(SEARCH($B21,LISTS!$H$1:$H$100))),0)*MATCH(1,--ISNUMBER(SEARCH($E21,LISTS!$I$1:$I$100))),0),MATCH(H$16,Lists!$H$1:$U$1,0)),"")

 

[jimbogarner]

Sorry only just got around to looking at this again, it still doesn't like that? Returns the people working on breakfast for every line...

 

[wjhladik]

=IFERROR(INDEX(LISTS!$H$1:$U$100,MATCH(1,--ISNUMBER(SEARCH($B21,LISTS!$H$1:$H$100))*--ISNUMBER(SEARCH($E21,LISTS!$I$1:$I$100)),0),MATCH(H$16,Lists!$H$1:$U$1,0)),"")

Changed the middle part