Nested IF Function and VLookup Alternatives
 

Hi Guys! I'm currently studying AS ICT at Sixth Form and needed to use a
nested if cell.....
=IF((G19=E7),B7,IF((E18=E8),B8,.............,IF((E 18=E16),B16))).
However, as you can see I have missed some of the IF values out. This
worked perfectly for all the values i could enter. But however there
was more than 10 if values and in the end it turned out you can not use
more than 7 :mad: . I was told on several websites if you should need
more than 10, you can use the VLookup Function. I then tried this only
to then find out the data in the column you are looking up for the
match (ie column E in my case) needs to be sorted in ascending order.
However, I am unable to do this as the column numbers differ constantly
each week, and are updated. Therefore, are there any other alternatives
to which are able to do the VLOOKUP or Nested If functions with more
than seven IF's and where the data does not need be sorted? I would
greatly appreciate it if anyone could help me with this. I would also
greatly appreciate it if anybody could take a look at my spreadsheet
and make any comments or suggestions, either good or bad in order to
imrpove it further. I can send it by email

Thanks,

Rob


--
robert.holmes
------------------------------------------------------------------------
robert.holmes's Profile: http://www.excelforum.com/member.php...o&userid=29587
View this thread: http://www.excelforum.com/showthread...hreadid=492894

Nested IF Function and VLookup Alternatives
 

The VLOOKUP has a fourth parameter called Range_Lookup. If this
is TRUE or omitted, the data must be in sorted order, and VLOOKUP
will return the largest value less than or equal to the specified
lookup value. If Range_Lookup is FALSE, the data does not need to
be sorted and VLOOKUP will return only an exact match.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"robert.holmes"

wrote in message
news:robert.holmes.1zy6uy_1134425703.6402@excelfor um-nospam.com...

Hi Guys! I'm currently studying AS ICT at Sixth Form and needed
to use a
nested if cell.....
=IF((G19=E7),B7,IF((E18=E8),B8,.............,IF((E 18=E16),B16))).
However, as you can see I have missed some of the IF values
out. This
worked perfectly for all the values i could enter. But however
there
was more than 10 if values and in the end it turned out you can
not use
more than 7 :mad: . I was told on several websites if you
should need
more than 10, you can use the VLookup Function. I then tried
this only
to then find out the data in the column you are looking up for
the
match (ie column E in my case) needs to be sorted in ascending
order.
However, I am unable to do this as the column numbers differ
constantly
each week, and are updated. Therefore, are there any other
alternatives
to which are able to do the VLOOKUP or Nested If functions with
more
than seven IF's and where the data does not need be sorted? I
would
greatly appreciate it if anyone could help me with this. I
would also
greatly appreciate it if anybody could take a look at my
spreadsheet
and make any comments or suggestions, either good or bad in
order to
imrpove it further. I can send it by email

Thanks,

Rob


--
robert.holmes
------------------------------------------------------------------------
robert.holmes's Profile:
http://www.excelforum.com/member.php...o&userid=29587
View this thread:
http://www.excelforum.com/showthread...hreadid=492894