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!