Variable index lookup based on cell value

Brass Contributor

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

 

9 Replies

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.

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.

image.png

 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.

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!

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.

Thanks a few people suggested indirect but the workbook needs to be open for that to work