Match or index as alternative to too many nested if functions

**macca345** · 06-09-2011, 03:44 AM

Hi guys, I have a set of data in three columns, F (text), G and H (numbers). F is sorted into column J with an array to remove blanks, and I need the other two columns to be sorted into K and L to match the correspond with their text label in F, which I’ve done with a nested formula in K1 as follows:

=IF(J1=F$1,G$1,IF(J1=F$2,G$2,IF(J1=F$3,G$3,IF(J1=F$4,G$4,IF(J1=F$5,G$5,IF(J1=F$6,G$6,IF(J1=F$7,G$7,"")))))))

This formula is then copied down to K7 and across into L. But as I need a lot more than 7 rows, is there an index or matching formula that would accomplish this?

Cheers

**TMS** · 06-09-2011, 03:52 AM

=IFERROR(VLOOKUP(J1,$F$1:$G$7,2,FALSE),"")

Regards

**macca345** · 06-14-2011, 06:46 AM

Hi TMShucks, I asked a bit of a variation on this question in a different thread and was told to use a match formula. Thanks for your help though, much appreciated.
Macca

**TMS** · 06-14-2011, 08:16 AM

You're welcome. Thanks for the rep.

Not tested:

=IFERROR(INDEX(G$1:G$7,MATCH(J1,$F$1:$F$7,0)),"")

vs

=IFERROR(VLOOKUP(J1,$F$1:$G$7,2,FALSE),"")

Your choice ;-)

Regards