| Home |
| Search |
| Today's Posts |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining VLOOKUP functions
Well, we are all guilty of that at times !! <g
Pete Toppers wrote: Pete, Apologies ... I must read the responses more carefully. "Pete_UK" wrote: Hi Toppers, Jay's question related to his original 30 records being 0 or blank, which he thought would return #N/A from the VLOOKUPs, so the ISNA part of my formula takes care of this, as you say. If there are blanks in the lookup tables themselves, then these will return 0 as a value, and if Jay wants these to appear blank then I find the easiest way is to set up conditional formatting to use a white foreground colour if the cell is zero - easier than amending the formula I gave him (which may well stretch to have 5 lookup tables at least). Pete Toppers wrote: Pete's solution handles the N/A error with his "Not Present" message. If you need to check for a genuine zero value you will need additional IF statements and you then exceed the Excel limit of 7 IFs. You can get round this by combining several IF statements but it becomes a complex statement. In Pete's formula you will need to replace (I think!) "not present", VLOOKUP(S5,table_3,2,0) with "not present", IF(VLOOKUP(S5,table_3,2,0)=0,"",VLOOKUP(S5,table_3 ,2,0) etc You can get around the 7 nested If statements limitation a few different ways. This web page gives direction on how to do it. http://www.cpearson.com/excel/nested.htm In my previous posting, I offered a VBA solution using a simply function which searches the tables. "Jay" wrote: Pete_UK: Thanks, that's what I was looking for, however, if any of the original 30 records are blank ("0" or "") records, I'd like for the results column to not show "0" or N/A or #value! error messages. How can I get that with your combined VLOOKUP function? Looking forward to your reply! Thanks muchly! "Pete_UK" wrote: As I understand it, you have 300,000 reference records - you want to consider this as one contiguous table. Because of Excel's limit of 65k rows, you will have to split this table up into at least 5 sub-tables - assume you give these names like table_1, table_2, table_3 etc., then you can combine multiple lookups as follows: =IF(ISNA(VLOOKUP(S5,table_1,2,0),IF(ISNA(VLOOKUP(S 5,table_2,2,0),IF(ISNA(VLOOKUP(S5,table_3,2,0),"no t present",VLOOKUP(S5,table_3,2,0)),VLOOKUP(S5,table _2,2,0)),VLOOKUP(S5,table_1,2,0)) Basically, if it exists in table_1 then get the corresponding data, otherwise if it is in table_2 get the data from there, otherwise try table_3 and if not there then return the error message. Hopefully you can see how this could be extended to more tables. Hope this helps. Pete Jay wrote: Hey Guy & Gals: To All the Excel guru's out there.... I have this Excel application which requires a combining of named ranges to accomplish what I need in values. What I have is for example 30 records on one sheet, but, on the VLOOKUP sheet (target) I may have as many as 300,000 records to search against for returned values which may match the data against the 30 records of the first sheet. The VLOOKUP function I currently have looks like so; =IF(ISNA(VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)),"",VLOOKUP(S5,'Tax Table'!$A$2:$D$33,2)). I know, this is only a partial list, however, I need to extend my VLOOKUP range searches to include various named ranges, since Excel only handles 65,000 records vertically. How can I pull it all together an have the VLOOKUP scan multiple ranges for values which may match those in the first 30 records as mentioned earlier? In the above example, I point to the worksheet named "Tax Table", however, there is also a named range called "IncomeTax", the other is "IncomeTax1" and "IncomeTax2 etc,etc,etc on the same sheet. How can this be done? Thanks, |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VLOOKUP Problem | Excel Discussion (Misc queries) | |||
| Combining functions | Excel Worksheet Functions | |||
| Combining IF AND & FIND Functions | Excel Worksheet Functions | |||
| Multiple IF & VLOOKUP functions | New Users to Excel | |||
| vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Threaded Mode
