Forum Discussion
Variable index lookup based on cell value
I am using a lookup below to look up a value from another worksheet. I would like a formula or Macro that will change the location of the Index/lookup based upon a cell value (C4)
I.e. If C4 says "170" I would like it to use the formula below but if C4's value is "204" then I want the lookup to do exactly the same thing with the exeption of the file the information is contained in will be located in a different directory with a different file name.
In all I have five files in five different workbooks in five different directorys. I want the user to select from a drop down list located in C4 and the index/lookup to recogise this and change accordingly. The range of the lookup is the same for all the workbooks, only the directory path and workbook file name changes.
I hope this makes sense!! Any help very much appreciated.
=IFERROR(INDEX('K:\JCSR\Marco\[170 Ops Centre.xlsm]pricelist'!$B$1:$B$65536,MATCH('FP&S Parts Form'!B11,'K:\JCSR\Marco\[170 Ops Centre.xlsm]pricelist'!$C$1:$C$65536,0)),"")
Have you tried a simple indented IF?
=IF(C4="";"";IF(C4=170;VLOOKUP(source170);IF(C4=240;VLOOKUP(source240);IF(et cetera);"")))
Got this working using the formula below but thanks for leading me in the right direction!
=IF(B26="TEXT1",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TEXT2",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TEXT3",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TEXT4",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TEXT5",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TEXT5",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TETXT6",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TEXT7",INDEX(Answer,MATCH(B10,Lookup)),IF(B26="TEXT8",INDEX(Answer,MATCH(B10,Lookup)),"NO RESULT")))))))))
On the formula above I am using a named range I have set (Answer and Match) and all the formulas are identical as this is only for the purpose of illustrating the soloution!
