Help with Match function to look across multiple rows/columns

JDMAS

New Member
Joined
Aug 14, 2014
Messages
13
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!
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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?
 
Upvote 0
How many rows might need to be searched? 2 or 20? (Looking for how complicated it needs to be.)
 
Upvote 0
It worked! I have no idea how, but it returned the cost! Thanks for the help!:ROFLMAO:

You wouldn't happen to know how this works, C Moore?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top