Nested IF Function and VLookup Alternatives

[robert.holmes]

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((E18=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 . 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

 

[Chip Pearson]

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"
<[email protected]>
wrote in message
news:[email protected]...