Help with Match function to look across multiple rows/columns

[JDMAS]

Hey everyone,

In my job, I have to find the cost of a customer across multiple sheets. The cost column does not appear in the same column every time, so I came up with this formula to find Cost and it seems to work:
=INDEX(INDIRECT("'" & B$1 & "'!A:D"),MATCH($A2,INDIRECT("'" & B$1 & "'!A:A"),0),MATCH("Cost",INDIRECT("'" & B$1 & "'!A1:D1"),0))

However, there are times where cost is not in the same row. When I try to expand the array of the Match function, it returns an NA. I have even tried SHIFT+CTRL+ENTER to make it an array, but sadly it does not work.

Is there anyway to make the Match function look across multiple rows/columns?

Thanks for any help!

 

[C Moore]

The short answer is no, Match is for single columns or single rows. So is it the 2nd match in your formula specifically that needs to be altered?

 

[JDMAS]

Yes. The 2nd Match function is where I am running into issues.

 

[C Moore]

How many rows might need to be searched? 2 or 20? (Looking for how complicated it needs to be.)

 

[JDMAS]

The most I have seen is 4 rows.

 

[C Moore]

From this post, http://www.mrexcel.com/forum/excel-questions/516742-how-return-results-position-2d-array.html , mikeericson's answer using sumproduct will work for the 2nd match (you need the column version).

 

[JDMAS]

It worked! I have no idea how, but it returned the cost! Thanks for the help!

You wouldn't happen to know how this works, C Moore?

 

[C Moore]

Take the range A1:C3 for example, 9 cells {A1,B1,C1,A2,B2,C2,A3,B3,C3}. If B2 had the value you were looking for (cell 5 of 9) sumproduct evaluates the first portion with true or false, but "--" converts false to 0 and true to 1. Giving {0,0,0,0,1,0,0,0,0}. The Column portion would be {1,2,3,1,2,3,1,2,3}. With a result of {0*1+0*2+0*3+0*1+1*2,+0*3......} which gives 2, the only non-zero result. In this case its the column number.