Alternative to nested IFs for > 8 arguments

[glad_ir]

Hi,

Can anybody help me with this one please?

I am using the formula below in a sheet to avoid duplicating entries if they match earlier rows and it's working well but I need to extend beyond 8 nested IFs

=IF($G19=" ","",IF($G19="","",IF($G19=$G18,"see above",IF($G19=$G17,"see above",IF($G19=$G16,"see above",IF($G19=$G15,"see above",IF($G19=G14,"see above",IF(G19=$G13,"see above",IF(G19=$G12,"see above",VLOOKUP($G19,$S$57:$U$68,2,0))))))))))

Below is the next formula I'd like to be able to write.....but too many IFs

=IF($G19=" ","",IF($G19="","",IF($G19=$G18,"see above",IF($G19=$G17,"see above",IF($G19=$G16,"see above",IF($G19=$G15,"see above",IF($G19=G14,"see above",IF($G19=$G13,"see above",IF($G19=$G12,"see above",IF($G19=$G11,"see above", VLOOKUP($G19,$S$57:$U$68,2,0)))))))))))

Is there a way to achieve the above?

Any help is much appreciated.

thank you,
Iain

 

[etaf]

first observation
Where you have the same TRUE result
Ie = "see above",

IF($G19=$G17,"see above",IF($G19=$G16,"see above",IF($G19=$G15,"see above",IF($G19=G14,"see above",IF($G19=$G13,"see above",IF($G19=$G12,"see above",IF($G19=$G11,
could be replaced with a countif

=countif($G11:$G18,$G19)>0 , “see Above”

these
=IF($G19=" ","",IF($G19="","",
same result ""
then
OR( $G19=" ", $G19 = "") ,

so
IF( OR( $G19=" ", $G19 = "") , "" , IF ( countif($G11:$G18,$G19)>0 , “see Above” , VLOOKUP($G19,$S$57:$U$68,2,0) ))

=IF( OR( $G19=" ", $G19 = ""),"", IF(COUNTIF($G11:$G18,$G19)>0, “see Above”, VLOOKUP($G19,$S$57:$U$68,2,0) ))
Does that do the same

will vlookup return an error at all ?
maybe
=IF( OR( $G19=" ", $G19 = ""),"", IF(COUNTIF($G11:$G18,$G19)>0, “see Above”, IFERROR(VLOOKUP($G19,$S$57:$U$68,2,0),"") ))

 

[Fluff]

How about

=IF(TRIM($G19)="","",IF(COUNTIFS($G11:$G18,$G19)>0,"see above", VLOOKUP($G19,$S$57:$U$68,2,0)))

 

[glad_ir]

first observation
Where you have the same TRUE result
Ie = "see above",

IF($G19=$G17,"see above",IF($G19=$G16,"see above",IF($G19=$G15,"see above",IF($G19=G14,"see above",IF($G19=$G13,"see above",IF($G19=$G12,"see above",IF($G19=$G11,
could be replaced with a countif

=countif($G11:$G18,$G19)>0 , “see Above”

these
=IF($G19=" ","",IF($G19="","",
same result ""
then
OR( $G19=" ", $G19 = "") ,

so
IF( OR( $G19=" ", $G19 = "") , "" , IF ( countif($G11:$G18,$G19)>0 , “see Above” , VLOOKUP($G19,$S$57:$U$68,2,0) ))

=IF( OR( $G19=" ", $G19 = ""),"", IF(COUNTIF($G11:$G18,$G19)>0, “see Above”, VLOOKUP($G19,$S$57:$U$68,2,0) ))
Does that do the same

will vlookup return an error at all ?
maybe
=IF( OR( $G19=" ", $G19 = ""),"", IF(COUNTIF($G11:$G18,$G19)>0, “see Above”, IFERROR(VLOOKUP($G19,$S$57:$U$68,2,0),"") ))

Hi Etaf, thank you for replying.

I get a #NAME error with the formula above. Fluff's formula works so I am all set.

Thank you again for replying.

Best regards,
Iain

 

[glad_ir]

How about

=IF(TRIM($G19)="","",IF(COUNTIFS($G11:$G18,$G19)>0,"see above", VLOOKUP($G19,$S$57:$U$68,2,0)))

Thank you for this. It works perfectly

Best,
Iain

 

[etaf]

I get a #NAME error with the formula above. Fluff's formula works so I am all set.

glad its working with @Fluff formula

not sure why yo uget a name error
=IF(OR( $G19=" ", $G19 = ""),"", IF(COUNTIF($G11:$G18,$G19)>0, "see Above", IFERROR(VLOOKUP($G19,$S$57:$U$68,2,0),"") ))
works OK in my Spreadsheet

But sorry about that

 

[Fluff]

Glad we could help & thanks for the feedback.