Forum Discussion

mark ainscough's avatar
mark ainscough
Brass Contributor
Jun 10, 2018

Variable index lookup based on cell value

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 (locati...
Formulas & Functions
tips and tricks
Joseph Shaw's avatar
Joseph Shaw
Copper Contributor
Jun 10, 2018

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 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jun 11, 2018

    Hi Joseph,

     

    INDIRECT doesn't work if another file isn't opened. And if it's opened you don't need file path.

    • mark ainscough's avatar
      mark ainscough
      Brass Contributor
      Jun 11, 2018

       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?

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jun 11, 2018

        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)),"")
)))))),
"")

         

Resources