Forum Discussion
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 (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
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 joinhttp://www.cetpainfotech.com/technology/advance-excel-training 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
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?
