Forum Discussion

mark ainscough's avatar
mark ainscough
Copper Contributor
Jun 07, 2018

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...
Macros & VBA
Need Help
Arul Tresoldi's avatar
Arul Tresoldi
Iron Contributor
Jun 07, 2018

Have you tried a simple indented IF?

=IF(C4="";"";IF(C4=170;VLOOKUP(source170);IF(C4=240;VLOOKUP(source240);IF(et cetera);"")))

  • mark ainscough's avatar
    mark ainscough
    Copper Contributor
    Jun 10, 2018

    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!

Resources