Forum Discussion
Variable index lookup based on cell value
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
Hi Joseph,
INDIRECT doesn't work if another file isn't opened. And if it's opened you don't need file path.
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?
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)),"") )))))), "")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.
