excelindexingmatchexcel-indirect

Excel Indirect function with Match

I am currently using the below formula which is returning an error.

What I am trying to do is select the entire column in the Price worksheet by using the indirect function and then return a value from that column based on 2 criteria's. I'm not sure where I'm going wrong. Any help would be much appreciated.

=INDEX(INDIRECT("Prices!J2:J2"),MATCH(F5,Prices!B:B,0),MATCH(D5,Prices!A:A,0))

The below code works for me but it is not robust. I'd like to get the INDIRECT working

=INDEX(Prices!BO:BO,MATCH(1,(D2=Prices!A:A)*(F2=Prices!$B:$B),0))

See error below:

Error Message

Thanks!

Solution

  • It looks like you are trying to look up the value from F2 in Column B and then return the corresponding entry in Column J. If so then this is the correct syntax:

    =INDEX(Prices!J:J,MATCH(F2,Prices!B:B,0),1)

    It also works if you add in INDIRECT:

    =INDEX(INDIRECT("Prices!J:J"),MATCH(F2,Prices!B:B,0),1)

    This website is a good reference for using index and match:

    https://exceljet.net/index-and-match

    Edit: some more relevant info from chat that helped the asker find his solution:

    To make the "J" Column in the above formula adjustable from Cell F2, concatenate the address like so:

    INDIRECT("Prices!"&J2&":"&J2)

    So, if you put "C" in Cell J2, then INDIRECT will reference "Prices!C:C" and then INDEX will look up your data from Column C.

    As for MATCH syntax, don't do this:

    =INDEX(INDIRECT("Prices!"&J2&":"&J2),MATCH(1,(F3=Prices!B:B)‌​*(D3=Prices!$A:$A),0‌​))

    Instead, concatenate F3 and D3, concatenate Column A and B data into Column A, and then do the MATCH, something like this:

    =INDEX(INDIRECT("Prices!"&J2&":"&J2),MATCH(D3&F3,Prices!A:A​,0‌​))