I have a single horizontal array and wish to "read" the contents of that array and return the value in the array. For example

The formulae in cell W5 is =IF(LOOKUP("LN",F5:R5)="LN","LN",IF(LOOKUP("EJ",F5:R5)="EJ","EJ","EJ")). I have 6 options of initials (LN,EJ,VM,DM,AS). My problem is that the formulae will not accept more than 2 nested IF statements. Is there an easier way to return the initials placed in the array. There is only ever on set of initials per row.

Re: lookup function

Try the Match function instead...
=IF(ISNUMBER(MATCH("LN",F5:R5,0)),"LN",IF(ISNUMBER(MATCH("EJ",F5:R5,0)),"EJ",IF(ISNUMBER(MATCH("VM",F5:R5,0)),"VM",IF(ISNUMBER(MATCH("DM",F5:R5,0)),"DM",IF(ISNUMBER(MATCH("AS",F5:R5,0)),"AS","none")))))
[EDIT]
Another way - entered as a array formula, using Ctrl+Shift+Enter ...
=IF(OR("LN"=F5:R5),"LN",IF(OR("EJ"=F5:R5),"EJ",IF(OR("VM"=F5:R5),"VM",IF(OR("DM"=F5:R5),"DM",IF(OR("AS"=F5:R5),"AS","none")))))
'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs  (n/a xl2013)
http://jmp.sh/K95N3ee

• Edited by James Cone 7 hours 24 minutes ago