Can annyone simplify this function?

[jherden]

Hi i really need help, this formula basically represents a multiple lookup function. So I and looking for a code in 1 of 3 different collumns. But it just seems like to much overkill? Any suggestions for shortening it? Would really appreciate it.

#IF(ISERROR(IF(ISERROR(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$D$6:$D$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$E$6:$E$21,0))),IF(ISERROR(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$D$6:$D$21,0))),(INDEX(Roster!$A$6:$A$21,MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0)))))#

this relates to a post from a couple of weeks ago
http://www.excelforum.com/excel-gene...ml#post2037422

[DonkeyOte]

What is the data type of the value being returned from Roster Column A - text/number etc ?

Could the lookup value be found in multiple columns at any one time or is it only ever once (or not at all)

[DonkeyOte]

Incidentally referring to your old post... the following in B16 would have worked I think:

Please Login or Register  to view this content.

For subsequent days the columns B:E would need to be altered to F:I etc...

Not easier mind...

There will be better methods... just need to think...

[martindwilson]

usually you can shorten it by replacing the whole index/match test with just match
IF(ISERROR(MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))
or turn it around with if(isnumber(
IF(Is number(MATCH('Pay Sheet fn 1'!B6,Roster!$C$6:$C$21,0))
which is easier to string together

[Ron Coderre]

This isn't pretty....

Please Login or Register  to view this content.

...but, does it help?

EDITED TO INCLUDE THIS SHORTER FORMULA:
I'm no fan of INDIRECT but....maybe it's ok in this case:

Please Login or Register  to view this content.

(I attached a sample file)

Does that help?

[DonkeyOte]

Another possibly simpler (??) version re: your old Q ... this assumes 1 match only per day

B16:

Please Login or Register  to view this content.

As per the other for each day you would need to alter the column references such that B:E becomes F:I etc...

[Ron Coderre]

Note: I added an alternative formula to that post.

[daddylonglegs]

So this is a different layout to the original?

For your original question you could use this formula in B16, confirmed with CTRL+SHIFT+ENTER and copied across and down to F23

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX($A$5:$A$11,SMALL(IF(OFFSET($B$5:$E$11,0,MATCH(B$15&"*",$B$3:$U$3,0)-1)=$A16,ROW($B$5:$E$11)-ROW($B$5)+1),1))))

see attached

Note: formula revised to return blank when initials don't exist for that day, using similar method to DonkeyOte's......