• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Problem with IFERROR and VLOOKUP formula

L

LisaP

New Member
Hi,


I'm new to this website, and I am an inexperienced Excel user.


Basically I have a worksheet with 4 sheets of information, and one called the Master List which collates information from the other 4. Sheets 1 to 4 are all personnel information from various years. I am trying to find an easy formula to be able to search sheets 1 to 4 for the 'End Date' using the surname as the search tool, but am getting 'Not Found' on every persons, even though I know most of them have dates.


I am using -


=IFERROR(VLOOKUP(A2,INDIRECT("'"&INDEX(List_of_Sheets,MATCH(TRUE,MATCH(INDIRECT("'"&List_of_Sheets&"'!"&Data_Range),A2)>0,0))&"'!"&Data_Range),6,0),"Not Found")


A2 being the cell containing the surname I am searching for and 6 being the Column No. I want to import the data from.


The List_of_Sheets has been created by typing in the actual names of the 4 sheets containing the information I need, and using Name Manager.


The Data_Range has been created also using Name Manager and not specifying the sheets, just the cells i.e. $A$2:$F$500, so it should check all 4 sheets.


Can someone please help me to find out what I am doing wrong?
 
WELCOME TO THE BOARDS!!


just my first flag while reading this, im not 100% sure if your data_range will default to all sheets by just indicating a cell range....
 
Hi LisaP!


Welcome to the forum..


Its hard to decide if the problem is what I am guessing or something else.. without seeing sample file.. still a try..


=IFERROR(VLOOKUP(A2,INDIRECT("'"&INDEX(List_of_Sheets,MATCH(1,--COUNTIF(INDIRECT("'"&List_of_Sheets&"'!"&Data_Range),A2)>0),0))&"'!"&Data_Range),6,0),"Not Found")


Not tested.. but believe will work..


Regards,

Deb
 
@Debraj Roy

Hi!

Am I missing something because I couldn't enter as a valid formula? I didn't try with the originally posted.

Regards!
 
@ SirJB7,


OOPS... thanks for checking..


@ LisaP..


Please check below post..

In OutputArea, #2 is formula for Vlookup from multiple Sheets.. (Sample file also there)..

http://chandoo.org/forums/topic/merge-two-lists#post-106314


Can you please update that formula and adapt accordingly..


Regards,

Deb
 
Hey guys,


thanks for your comments.


Unfortunately I tried all options, including that link you kindly sent, but still no luck.


As ths is the first time I've used this website, how and where do I upload my Excel file so someone can have a look at it?
 
hi lisap,


use the below mentioned link to post your file.


http://www.fileconvoy.com/ and then paste the link after uploading your file,so that we can have a clearer look at your problem.


Regards,

Rahul
 
Hey Rahul,


Thank you, here's the link


http://www.fileconvoy.com/dfl.php?id=gbb7c0c90114fc54a999301910e306e1ae3e2a3313
 
Hi Lisa!


OOPS my fault..


try this..


{=IFERROR(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A:$A"),$A2)>0),0))&"'!$A:$H"),6,FALSE),"Not Found")}


with Ctrl + Shift + Enter.


https://dl.dropboxusercontent.com/u/78831150/Excel/problem-with-iferror-and-vlookup-formula%20%28LisaP%29.xls


Regards,

Deb
 
Hey Rahul,


Thank you for this, but I have to be honest and say I don't really understand what I'm looking at, and how to transfer this/what formula etc. to my spreadsheet.


From your worksheet - VLOOKUP Array - I tried to 'copy' the formula from Column B and adpated using my SheetList, but I always get #Value! warning regardless.


Also I note that your formula has extra { } - I tried to get this by pressing Control+Shift+Enter, but no luck


What am I doing wrong?


If this is taking up too much of your time, the other option which is your formula in the VLOOKUP sheet Column B (which is what I currently have in my column I of Master List, would suit fine, but if there is no data the #N/A comes up - is there an easy way to get it so that, if there is no information, the cell is left blank using the same formula?
 
Hi Lisa ,


Can you try this ?


=IFERROR(VLOOKUP($A2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$2:$AK$200"),$A2)>0),0))&"'!$A$2:$AK$200"),6,FALSE),"")


entered as an array formula , using CTRL SHIFT ENTER.


Narayan
 
Yeah!!!!


Thank you, that works now, brilliant!


Wondering if I can be cheeky and ask one for another piece of advice?


I noticed that, although my original search goes by Surname to get the Leaving Date, that there is more than one person with the same Surname (how stupid of me not to realise!) and therefore I would like to add into this formula a second search parameter, in this case by their First Name, and if possible a third parameter (just in case) where it also then searches by their Employment Number.


Is this possible?
 
Hi Lisa ,


Sure ; however , since you want to use a concatenation of 3 cells , it would be better to use helper columns ; what I did was make use of any unused column , sau column AO , and put the following formula in all the individual worksheets :


=$A2&"|"&$B2&"|"&$C2


What this does is concatenate all the three cells A2 , B2 and C2.


We can now use this column in the Master List tab to lookup in all the individual sheets.


In column J on the Master List tab , starting from J2 , put in the following array formula , using CTRL SHIFT ENTER :


=IFERROR(OFFSET(INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$AO$2:$AO$200"),$AO2)>0),0))&"'!$F$2:$F$200"),MATCH($AO2,INDIRECT("'"&INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$AO$2:$AO$200"),$AO2)>0),0))&"'!$AO$2:$AO$200"),0)-1,),"")


This should give you the matching dates from column F of the individual worksheets ; in case you want any other relevant data from these worksheets , change the reference $F$2:$F$200 to which ever column you want data from.


Narayan
 
Hey Narayan,


Thank you. Unfortunately I can't get it to give me the dates, it always comes up as empty i.e. no date, and I sure it's cause I'm doing something wrong.


In the formula, SheetList&"'!$AO$2:$AO$200 - do I need to specify which worksheets it is to pick up the information from the AO column (where I put the first suggestion (=$A2&"|"&$B2&"|"&$C2)? Or should it automatically check?
 
Hi Lisa ,


Can you check your file here ?


https://www.dropbox.com/s/rtqrxuvx6aw9v8a/LEAVERS%20LIST.xlsx


The concatenation has been done in all the worksheets , in column AO.


The formulae in the Master List tab now use the data in column AO instead of the data in column A.


Narayan
 
Hi Lisap,


Can you send me this

https://www.dropbox.com/s/rtqrxuvx6aw9v8a/LEAVERS%20LIST.xlsx

file link on http://www.fileconvoy.com/ which narayank991 has send to you.


Regards,

Rahul
 
Hi Rahul ,


The file is available at this link :


http://www.fileconvoy.com/dfl.php?id=g7025241e73c4b5d89993025195038cfc1f2fddea7


Narayan
 
narayank991 in masterlist i am not understanding the formula from a2 to h2 like for example in h2 its


=IF(ISBLANK('C:Documents and SettingsuserMy DocumentsDownloads[NEW STARTER LIST.xls]2007-2009'!P2),"",'C:Documents and SettingsuserMy DocumentsDownloads[NEW STARTER LIST.xls]2007-2009'!P2)
 
Hi Rahul ,


Those formulae are not mine , they are Lisa's ; you will have to check up with her.


The link is an external link , and it is referring to a path on my computer , where in the original it would refer to a path on Lisa's computer.


Narayan
 
Hi Lisap,


Can you explain me in your masterlist sheet from a2:h2 you have used the below mentioned formula.


=IF(ISBLANK('C:Documents and SettingsuserMy DocumentsDownloads[NEW STARTER LIST.xls]2007-2009'!P2),"",'C:Documents and SettingsuserMy DocumentsDownloads[NEW STARTER LIST.xls]2007-2009'!P2)


kindly clarify how you have used this formula.
 
@Rahul


Hi


as per my understand Mr. Lisap getting the result from closed workbook to Levers workbook in the Sheet Masterlit Range A2


1. C:Documents and SettingsuserMy DocumentsDownloads is the path for the workbook for New Starter List.xls


2. 2007-2009' it is the Sheet Name


3. P2 it is the Range


most probably ISBLANK Formula is used in 2003 when we are using the ISBLANK then the formula to be repeated


thanks


SP
 
You must log in or register to reply here.
Back
Top