# Vlookup (or similar) that matches 2 criteria

#### K_Clark

##### New Member
Hi everyone,

I have a workbook that I would like to link to another workbook. The issue that I'm having is the lookup formula must satisfy 2 different criteria. It must match column A (dept number) and Row 9 (Business that the cost is being allocated to) in the main workbook. In the workbook that I'm linking to it must match the information in Column A (dept number) and B (business that the cost is being allocated to), but pull the data from Column AA (Total amount allocated). I have been playing with Index & Match formulas with no luck. Any suggestions?

Thanks,
Kris

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hi everyone,

I have a workbook that I would like to link to another workbook. The issue that I'm having is the lookup formula must satisfy 2 different criteria. It must match column A (dept number) and Row 9 (Business that the cost is being allocated to) in the main workbook. In the workbook that I'm linking to it must match the information in Column A (dept number) and B (business that the cost is being allocated to), but pull the data from Column AA (Total amount allocated). I have been playing with Index & Match formulas with no luck. Any suggestions?

Thanks,
Kris
Here's an example.

Book1
ABC
2A1Result1
3A2Result2
4A3Result3
5B1Result4
6B2Result5
7B3Result6
8C1Result7
9C2Result8
10C3Result9
11___
12B2Result5
Sheet1

We want to look up B in column A and 2 in column B.

This array formula** entered in C12:

=INDEX(C2:C10,MATCH(B12,IF(A2:A10=A12,B2:B10),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thanks for the quick reply. I entered the following formula =INDEX(\$AA\$4:\$AA\$994,MATCH(\$A10,IF(\$A\$4:\$A\$994=\$A10,\$B\$4:\$B\$994=G\$8),0)). I am getting a #N/A error. When used the Evaluate Formula feature it showed the error coming from the IF statement. The formula returned a "False" on the Logic Test portion of the formula. It looks like it's trying to compare all of the results of that range at once.

Thanks for the quick reply. I entered the following formula =INDEX(\$AA\$4:\$AA\$994,MATCH(\$A10,IF(\$A\$4:\$A\$994=\$A10,\$B\$4:\$B\$994=G\$8),0)). I am getting a #N/A error. When used the Evaluate Formula feature it showed the error coming from the IF statement. The formula returned a "False" on the Logic Test portion of the formula. It looks like it's trying to compare all of the results of that range at once.
Maybe this...

Array entered**:

=INDEX(\$AA\$4:\$AA\$994,MATCH(1,IF(\$A\$4:\$A\$994=\$A10,IF(\$B\$4:\$B\$994=G\$8,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I'm still getting the same result. I have tried to adjust the formula showing an IF(AND(....)) and to see if that changes anything but that seems to be returning the same result. In the IF statement it is returning "True" for the rows that contain the correct information, but it seems to be comparing all items found in the IF and can't seem to identify the one that I am looking for.

I'm still getting the same result. I have tried to adjust the formula showing an IF(AND(....)) and to see if that changes anything but that seems to be returning the same result. In the IF statement it is returning "True" for the rows that contain the correct information, but it seems to be comparing all items found in the IF and can't seem to identify the one that I am looking for.
Diregarding the data and the range locations, does the exhibit in post #2 demonstrate what you're wanting to do?

Diregarding the data and the range locations, does the exhibit in post #2 demonstrate what you're wanting to do?

Yes. That is exactly what I'm attempting to do. I need to find the Total Amount Allocated to 10 different businesses for almost 70 cost centers.

Yes. That is exactly what I'm attempting to do. I need to find the Total Amount Allocated to 10 different businesses for almost 70 cost centers.
Ok, then either of the formulas I've already suggested should do what you want.

Since you can't either one to work it would be better if you could post some sample data and tell us what result you expect.

Workbook 1
<TABLE style="WIDTH: 339pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=452 x:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2688" width=84><COL style="WIDTH: 54pt" span=3 width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 60pt; HEIGHT: 14.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33 height=19 width=80>Cost Center</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8 0.5pt" class=xl36 width=72>BU1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8 0.5pt" class=xl34 width=84>BU2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8 0.5pt" class=xl34 width=72>BU3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #ece9d8 0.5pt" class=xl34 width=72>BU4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: #ffff99; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl35 width=72>BU5</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30 height=19 x:num>111111</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl37> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl28> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl28> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl28> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29> </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=19 x:num>222222</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl38> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 height=19 x:num>333333</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl38> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl24> </TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25> </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 height=19 x:num>444444</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl39> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl26> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl26> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8 0.5pt" class=xl26> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27> </TD></TR></TBODY></TABLE>

Workbook 2
<TABLE style="WIDTH: 177pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=236 x:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2688" width=84><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 height=34 width=80>Cost Center</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 54pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl27 width=72>Business Unit</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 63pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl26 width=84>Actual FY'11</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=19 x:num>111111</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl28>BU1</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl29 x:num="150"> 150 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>111111</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU2</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="200"> 200 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>111111</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU3</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="98"> 98 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>111111</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU4</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="15"> 15 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>111111</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU5</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="5165"> 5,165 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>222222</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU1</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="651"> 651 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>222222</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU2</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="9844"> 9,844 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>222222</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU3</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="51615"> 51,615 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl25 height=19 x:num>222222</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl30>BU4</TD><TD style="BORDER-BOTTOM: #ece9d8 0.5pt; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl31 x:num="56165"> 56,165 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl32 height=19 x:num>222222</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl33>BU5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid" class=xl34 x:num="844"> 844 </TD></TR></TBODY></TABLE>

I would like to have "workbook 1" search for the Cost Center and Business Unit in "workbook 2" and give me the FY'11 value.

Ex:cost center - 222222 & BU3 should return\$51,165

Workbook 1
<TABLE style="WIDTH: 339pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=452 border=0 x:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2688" width=84><COL style="WIDTH: 54pt" span=3 width=72><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffff99" width=80 height=19>Cost Center</TD><TD class=xl36 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=72>BU1</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=84>BU2</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=72>BU3</TD><TD class=xl34 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=72>BU4</TD><TD class=xl35 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99" width=72>BU5</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>111111</TD><TD class=xl37 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>222222</TD><TD class=xl38 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>333333</TD><TD class=xl38 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>444444</TD><TD class=xl39 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8 0.5pt; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Workbook 2
<TABLE style="WIDTH: 177pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=236 border=0 x:str><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 2688" width=84><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 60pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=80 height=34>Cost Center</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 54pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=72>Business Unit</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 63pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=84>Actual FY'11</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>111111</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU1</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="150">150 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>111111</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU2</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="200">200 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>111111</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU3</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="98">98 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>111111</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU4</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="15">15 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>111111</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU5</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="5165">5,165 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>222222</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU1</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="651">651 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>222222</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU2</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="9844">9,844 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>222222</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU3</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="51615">51,615 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #ece9d8 0.5pt; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>222222</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent">BU4</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #ece9d8 0.5pt; BACKGROUND-COLOR: transparent" x:num="56165">56,165 </TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl32 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19 x:num>222222</TD><TD class=xl33 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">BU5</TD><TD class=xl34 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="844">844 </TD></TR></TBODY></TABLE>

I would like to have "workbook 1" search for the Cost Center and Business Unit in "workbook 2" and give me the FY'11 value.

Ex:cost center - 222222 & BU3 should return\$51,165
Ok, like this...

Data:

Book1
ABC
10111111BU1150
11111111BU2200
12111111BU398
13111111BU415
14111111BU55165
15222222BU1651
16222222BU29844
17222222BU351615
18222222BU456165
19222222BU5844
Sheet1

Summary:

Book1
ABCDEF
1_BU1BU2BU3BU4BU5
211111115020098155165
322222265198445161556165844
Sheet1

This array formula** entered in B2:

=INDEX(\$C\$10:\$C\$19,MATCH(1,IF(\$A\$10:\$A\$19=\$A2,IF(\$B\$10:\$B\$19=B\$1,1)),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to F2 then down as needed.

Replies
3
Views
178
Replies
3
Views
187
Replies
2
Views
585
Replies
3
Views
159
Replies
3
Views
297

1,206,757
Messages
6,074,760
Members
446,084
Latest member
WalmitAal

### 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.

### Which adblocker are you using?

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