Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 . 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Function | Excel Discussion (Misc queries) | |||
How do I access data stored in a SQL server for vlookup function? | Excel Worksheet Functions | |||
VLOOKUP Function using Data Ranges. | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Nested if, sum & vlookup Function | Excel Worksheet Functions |