Changing Index Match Lookup Value

  • Could this be possible to change the lookup value based on what letter appears in a certain cell.


    Here's a lookup I've got.



    =IF(ISERROR(INDEX(D:D,MATCH(D2,C:C,0))),"",INDEX(D:D,MATCH(D2,C:C,0)))



    So if for instance in cell G2 the letter changes to an E then get the lookup to change the value in the formula to E2 instead of D2 so it appears like,



    =IF(ISERROR(INDEX(D:D,MATCH(E2,C:C,0))),"",INDEX(D:D,MATCH(E2,C:C,0)))



    and then if the cell text changes to an D change the lookup value back to D2.


    It may sound like a strange request, but I'm trying to speed up a spreadsheet up at work where their computers are not the fastest things around.


    And thanks if any one can help if possible :)

  • Re: Changing Index Match Lookup Value


    Hi balders,


    Assuming that you're using a relatively recent version of Excel, which supports the IFERROR function, try:


    =IFERROR(INDEX(D:D,MATCH(INDIRECT(G2&ROW()),C:C,0),1),"")


    Hope this helps.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Changing Index Match Lookup Value



    At home I have Excel 2010 but at work we are still stuck in the dark ages with 2003 !! Shame really as that looks just what I'm looking for.
    You wouldn't have a formula that would work in 2003 have you?


    And thanks for the swift reply :)

  • Re: Changing Index Match Lookup Value


    You can use the same principle of INDIRECT, but you will have to go back to using your original IF(ISERROR( format. The IFERROR function avoids Excel having to do the same work twice.


    =IF(ISERROR(INDEX(D:D,MATCH(INDIRECT(G2&ROW()),C:C,0),1)),"",INDEX(D:D,MATCH(INDIRECT(G2&ROW()),C:C,0),1))

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Changing Index Match Lookup Value


    Or maybe just test the MATCH() part to begin with, then do the full INDEX/MATCH if the MATCH is TRUE?


    Also, maybe try and avoid full column referenced with INDIRECT() as it will slow your file down eventually, so...
    =IF(ISERROR(MATCH(INDIRECT(G2&ROW()),$C$1:$C$1000,0)),"",INDEX(D:D,MATCH(INDIRECT(G2&ROW()),$C$1:$C$1000,0)))

  • Re: Changing Index Match Lookup Value


    Well i like to say a very big thanks to all that has helped, discovering how easy it is to use the INDIRECT function has transformed a slow workbook in to a lightning fast one.
    I have now adopted the INDIRECT function into a large amount of my formula's.


    Shame I can't buy you lot a drink, so instead I like to wish you a very good weekend :)


    And Thanks Again !! :)

  • Re: Changing Index Match Lookup Value


    Taking this a little further, is is possible to use the INDIRECT function to change what columns it is looking up by what letters appear in a certain referenced cell


    For instance changing


    =IF(ISERROR(INDEX(D:D,MATCH(A2,C:C,0),1)),"",INDEX(D:D,MATCH(INDIRECT(G2&ROW()),C:C,0),1))

    to


    =IF(ISERROR(INDEX(F:F,MATCH(A2,E:E,0),1)),"",INDEX(F:F,MATCH(INDIRECT(G2&ROW()),E:E,0),1))



    Edit that, I solved it myself, I found if I input the range name in referenced cell, the indirect function returns the results that I require.

  • Re: Changing Index Match Lookup Value


    Quote from FDibbins;768006

    Another option would be to use the 2nd MATCH criteria in INDEX to select the column to use


    Could you explain this further for me?


    Thanks :)

  • Re: Changing Index Match Lookup Value


    Index() syntax is =INDEX(range, row, column)


    If range is a single column (or row), you can leave off the , column (or , row)


    What makes INDEX so useful, is that if you couple it with MATCH, you can get MATCH to return the row or column number for you.


    Now, if you need to find the row number AND the column number, you just need to increase the INDEX range to include all your columns and rows, then use MATCH to return the intersect...


    1. Single column range...
    =INDEX(A1:A10,2)
    returns the value in A2


    2. Single row range...
    =INDEX(A1:J1,2)
    returns the value in B1


    3. Multiple row and column range...
    =INDEX(A1:J10,2,2)
    returns the value in B2


    [Table="width:, class:grid"]

    [tr][td]

    [/td][td]

    A

    [/td][td]

    B

    [/td][td]

    C

    [/td][td]

    D

    [/td][td]

    E

    [/td][/tr]


    [tr][td]

    1

    [/td][td]

    1

    [/td][td]

    10

    [/td][td]

    100

    [/td][td]

    1000

    [/td][td]

    10000

    [/td][/tr]


    [tr][td]

    2

    [/td][td]

    2

    [/td][td]

    20

    [/td][td]

    200

    [/td][td]

    2000

    [/td][td]

    20000

    [/td][/tr]


    [tr][td]

    3

    [/td][td]

    3

    [/td][td]

    30

    [/td][td]

    300

    [/td][td]

    3000

    [/td][td]

    30000

    [/td][/tr]


    [tr][td]

    4

    [/td][td]

    4

    [/td][td]

    40

    [/td][td]

    400

    [/td][td]

    4000

    [/td][td]

    40000

    [/td][/tr]


    [tr][td]

    5

    [/td][td]

    5

    [/td][td]

    50

    [/td][td]

    500

    [/td][td]

    5000

    [/td][td]

    50000

    [/td][/tr]


    [tr][td]

    6

    [/td][td]

    6

    [/td][td]

    60

    [/td][td]

    600

    [/td][td]

    6000

    [/td][td]

    60000

    [/td][/tr]


    [tr][td]

    7

    [/td][td]

    7

    [/td][td]

    70

    [/td][td]

    700

    [/td][td]

    7000

    [/td][td]

    70000

    [/td][/tr]


    [tr][td]

    8

    [/td][td]

    8

    [/td][td]

    80

    [/td][td]

    800

    [/td][td]

    8000

    [/td][td]

    80000

    [/td][/tr]


    [tr][td]

    9

    [/td][td]

    9

    [/td][td]

    90

    [/td][td]

    900

    [/td][td]

    9000

    [/td][td]

    90000

    [/td][/tr]


    [tr][td]

    10

    [/td][td]

    10

    [/td][td]

    100

    [/td][td]

    1000

    [/td][td]

    10000

    [/td][td]

    100000

    [/td][/tr]


    [tr][td]

    11

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [tr][td]

    12

    [/td][td]

    2

    [/td][td]

    =INDEX(A1:A10,2)

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [tr][td]

    13

    [/td][td]

    10

    [/td][td]

    =index(A1:E1,2)

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [tr][td]

    14

    [/td][td]

    20

    [/td][td]

    =index(A1:E10,2,2)

    [/td][td]

    [/td][td]

    [/td][td]

    [/td][/tr]


    [/table]

  • Re: Changing Index Match Lookup Value


    Thanks for the detailed explanation, this also looks very useful in the workbook I have up and running at work, looks like I just might to do some more adjustments to it and implement some of these formula's.


    Thanks for explaining it to me :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!