Log In
Register
Home
New Member
August 18, 2019
Offline
Hello,
I have to use vlookup function in a excel file to look for data in any one of the five sheets in another excel file. The above IFERROR formula does not work.
In excel file Test1 I require the value for cell A2 that reads as ABCAPITAL~EQ from excel file Test2 where value for ABCAPITAL~EQ is in one of the sheets in the cell G7. So I use the formula in cell B2 in excel file Test1 as below:
=IFERROR(VLOOKUP(SUBSTITUTE(A2,"~","~~"),[Test2.xlsx]Sheet1!$A$2:$G$2,7,FALSE),IFERROR(VLOOKUP(SUBSTITUTE(A2,"~","~~"),[Test2.xlsx]Sheet2!$A$2:$G$2,7,FALSE),IFERROR(VLOOKUP(SUBSTITUTE(A2,"~","~~"),[Test2.xlsx]Sheet3!$A$2:$G$2,7,FALSE))))
I get the error message “You have entered too few arguments for this function. To get help with this function, click OK to close the message. Then click the Insert Function button located to the left of the equal sign in your formula.
July 16, 2010
Offline
Hi Tundul,
You're missing the last IFERROR value_if_error argument in the formla that's required before the third closing parenthesis.
=IFERROR(VLOOKUP(SUBSTITUTE(A2,"~","~~"),[Test2.xlsx]Sheet1!$A$2:$G$2,7,FALSE),IFERROR(VLOOKUP(SUBSTITUTE(A2,"~","~~"),[Test2.xlsx]Sheet2!$A$2:$G$2,7,FALSE),IFERROR(VLOOKUP(SUBSTITUTE(A2,"~","~~"),[Test2.xlsx]Sheet3!$A$2:$G$2,7,FALSE),"")))
That said, I would not use a formula to extract this data. I would use Power Query to consolidate the data into the correct tabular format. From there getting the data you want is much simpler and less problematic than working with formulas that reference 3D ranges in external workbooks.
Mynda
1 Guest(s)
