Jun 10 2018
09:02 AM
- last edited on
Jul 31 2018
08:25 AM
by
TechCommunityAP
Jun 10 2018
09:02 AM
- last edited on
Jul 31 2018
08:25 AM
by
TechCommunityAP
I am using the formula below
=IFERROR(INDEX([170opscentre.xlsx]Sheet2!$C$2:$C$11,MATCH(B10[170opscentre.xlsx]Sheet2!$B$2:$B$11,0)),"")
Works fine but is it possible to swap out the array (location on a shared drive) with a value contained within a cell? I have tried the below.
=IFERROR(INDEX("D1",MATCH(B10,VALUE="C1",0)),"")
In this case the cell D1 would would contain the array "K:\170opscentre.xlsx]sheet2!$C$2:$C$11" and C1 would contain the array "K:\170opscentre.xlsx]sheet2!$B$2:$B$11"
So the formula's are the same but the arrays for the formula are contained within a cell rather than the formula and can be altered within the cell without editing the formula itsself!
Note C1 and D1 will contain a lookup which alters so the correct Index is carried out! Hope this makes sense!!
Thanks
Jun 10 2018 04:05 PM
Hi Mark,
Yes use the indirect() function so your formula will look like this:
=IFERROR(INDEX("D1",MATCH(B10,INDIRECT(C1, TRUE),0)),"")
That should work for you.
Best
Joe
Jun 11 2018 02:45 AM
Hi Joseph,
INDIRECT doesn't work if another file isn't opened. And if it's opened you don't need file path.
Jun 11 2018 02:14 PM
Thanks for response Guy.
I do need it work without opening the workbook. I have found a work round using the formula below a bit long winded but its functional my only problem with this is I want a blank value if no result is found and the formula is too long for an iferror command or at least thats what excel tells me.
=IF(Estimate!C4="1",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="2",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="3",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="4",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="5",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),IF(Estimate!C4="6",INDEX('[Bibbys Ops Center.xlsm]FPS_Parts'!$F$3:$F$90000,MATCH(Estimate!B49,'[Bibbys Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0)),IF(Estimate!C4="7",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),"")))))))
Currently I only get a blank value if the Value contained within Estimate!C4 is not parts of the formula but if Estimate!C4 value is either 1,2,3,4,5,6,7 and no result is found then it returns a #N/A.otherwise it works
Is there a way to do this so that I get a blank value if no result is found? Should I be looking at a Macro instead? and if so can this lookup a closed workbook in the same way as this formula?
Jun 11 2018 02:32 PM
Mark, you may wrap your formula with IFERROR or with IFNA like
=IFERROR( IF(Estimate!C4="1",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)), IF(Estimate!C4="2",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)), IF(Estimate!C4="3",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)), IF(Estimate!C4="4",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)), IF(Estimate!C4="5",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)), IF(Estimate!C4="6",INDEX('[Bibbys Ops Center.xlsm]FPS_Parts'!$F$3:$F$90000,MATCH(Estimate!B49,'[Bibbys Ops Center.xlsm]FPS_Parts'!$B$3:$B$90000,0)), IF(Estimate!C4="7",INDEX('[170 Ops Centre 2018.xlsm]FPS_Parts'!$F$3:$F$9000,MATCH(Estimate!B49,'[170 Ops Centre 2018.xlsm]FPS_Parts'!$B$3:$B$9000,0)),"") )))))), "")
Jun 12 2018 10:35 PM
Hi Sergei, thanks for clarifying the use of INDIRECT.
Mark has said that excel is informing him that his formula is too long to wrap in an IFERROR.
Mark! You could try to store your formula as a defined name in the name manager, and in-cell use the IFERROR following that, such as:
IFERROR(my_long_formula, "")
I don't know if that will definitely work however, give it a try and let us know.
Jun 13 2018 07:47 AM - edited Jun 13 2018 07:51 AM
Hi Joseph,
Sorry, I missed that. On the other hand formula limit is 8192 characters, at least for any Excel version starting from 2007 https://support.office.com/en-us/article/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269...
Formula with IFERROR is bit more than thousand characters, it shall be no issue. I put it Excel imitating two files which are referencing, also no problems. It works, at least from syntaxes point of view.
UPDATE
I did above with opened files. If close, full path will be added to file names within the formula. In my case it increases the formula length on another 500 characters, also no issues with that.
Jun 13 2018 01:33 PM
Thanks Guys I found work round for the time being by transferring the result to another cell using another formula removing the NA# (This is on hidden worksheet to keep it neat.)
I did try Named ranges but the formula still too long (The formula I pasted is not the complete formula) I edited it down before posting!
Jun 14 2018 06:23 AM - edited Jul 10 2018 10:15 PM
VLOOKUP($B2,INDIRECT(""&C$4&"!"&"B2:C12"),2,0)
By Above formula you can lookup based your values in cell. If you want to learn more basic to advanced level concepts then you can join advance excel training course where you will learn all things related to all excel formulas etc.
Jun 21 2018 04:10 PM
Thanks a few people suggested indirect but the workbook needs to be open for that to work