Help with Match function to look across multiple rows/columns

JDMAS

New Member
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

C Moore

Well-known Member
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

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

C Moore

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

JDMAS

New Member
The most I have seen is 4 rows.

JDMAS

New Member
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

Well-known Member
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.

Replies
7
Views
641
Replies
0
Views
178
Replies
1
Views
658
Replies
1
Views
704
Replies
1
Views
580

1,191,628
Messages
5,987,768
Members
440,109
Latest member
mitra2022

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.

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

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