CODEWITHSUNDEEP
excelindexingmatchexcel-indirect
PythonBeginner
PythonBeginner

Reputation: 483

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!

Upvotes: 0

Views: 5064

Answers (1)

bl33p bl00p
bl33p bl00p

Reputation: 71

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‌​))

Upvotes: 0

Related Questions