Array formula (again) that looks up more than one item

[Sam Longstaff]

Hi,

I am using this formula: {=IFERROR(INDEX(April!$A$10:$A$45,MATCH('Key and guidance'!$C$35,INDEX(April!$AI$10:$BM$45,,MATCH('Master call list'!$C9,April!$AI$8:$BM$8,0)),0)),"")}

This looks up a table and returns the name from A10:A45 that has the text referenced in 'Key and guidance'!C35.

How can I amend this formula so that if that referenced text is not found in the column that the formula looks in the formula then picks out an alternative text that I am interested in. This would be referenced by 'Key and guidance'!C37.

Can I build the formula to include more than two referenced texts I wish to search for? See attached file.

Many thanks in advance!

 

[bosco_yip]

Try this (non-testing),

{=IFERROR(INDEX(April!$A$10:$A$45,LOOKUP(9^9,MATCH(IF({1,0},'Key and guidance'!$C$35,'Key and guidance'!$C$37),INDEX(April!$AI$10:$BM$45,,MATCH('Master call list'!$C9,April!$AI$8:$BM$8,0)),0))),"")}

Regards
Bosco

 

[Sam Longstaff]

That's great and works, thanks. How would I adjust it to then add a third text item to look for?

 

[bosco_yip]

That's great and works, thanks. How would I adjust it to then add a third text item to look for?

In adding a third text item (e.g. C39), try this (non-testing)

{=IFERROR(INDEX(April!$A$10:$A$45,LOOKUP(9^9,MATCH(CHOOSE({1,2,3},'Key and guidance'!$C$35,'Key and guidance'!$C$37,'Key and guidance'!$C$39),INDEX(April!$AI$10:$BM$45,,MATCH('Master call list'!$C9,April!$AI$8:$BM$8,0)),0))),"")}

Regards
Bosco

 

[Sam Longstaff]

Bosco, Brilliant thank you!

I have another that perhaps you could be kind enough to help me with?

This formula {=IFERROR(INDEX(April!$AI$8:$BL$8,SMALL(IF(INDEX(April!$AI$10:$BM$45,MATCH($A$3,April!$A$10:$A$45,0),)=L$26,COLUMN(April!$AI$8:$BM$8)-COLUMN(April!$AI$8)+1),ROW(C1))),"")}


looks up my range to find the text in L$26. How could I amend this formula so that it if it does not find this referenced text it looks up another text that I have defined?

I know this is very similar to the question I first asked, I just don't have the skills yet. Hopefully you can help?!

 

[bosco_yip]

What does "another text" meaning?

I cannot give help unless further information is given.

Regards
Bosco

 

[Sam Longstaff]

So the first text item "ON CALL" is defined by L$26, if I also wanted it to look for "AL CALL" (say I put "AL CALL" in L$25) if "ON CALL" is not found.

Thanks Bosco

 

[bosco_yip]

So the first text item "ON CALL" is defined by L$26, if I also wanted it to look for "AL CALL" (say I put "AL CALL" in L$25) if "ON CALL" is not found.

Thanks Bosco

Then, try this (non-testing) :

{=IFERROR(INDEX(April!$AI$8:$BL$8,SMALL(IF(ISNUMBER(MATCH(INDEX(April!$AI$10:$BM$45,MATCH($A$3,April!$A$10:$A$45,0),),L$25:L$26,0)),COLUMN(April!$AI$8:$BM$8)-COLUMN(April!$AI$8)+1),ROW(C1))),"")}

Regards
Bosco