Index Match with 3 Criteria

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
I have searched for a solution but am coming up blank. I have two worksheets that I need to do a search From one, using 3 criteria, to the Other sheet and bring in the value that matches.

Sheet with the Formula that I tried:

The sheet with the Values that I need to bring in to Row 9:


Excel 2010
CDE
4TESTTESTTEST
587/4587/C87/45
68001A8001A8001A
7LHSTSC
8105020
9#VALUE!#VALUE!#VALUE!
Crew 4
Cell Formulas
RangeFormula
C9{=INDEX(LABOR!$A$3:$E$21,MATCH(C5,C7&C8,),0)}
D9{=INDEX(LABOR!$A$3:$E$21,MATCH(D5,D7&D8,),0)}
E9{=INDEX(LABOR!$A$3:$E$21,MATCH(E5,E7&E8,),0)}
Press CTRL+SHIFT+ENTER to enter array formulas.



Excel 2010
ABCDE
2Lot / BlockAddressCodeDescriptionBudget
387/45222 ANY STREETLH10Lath Labor1,100.00
4LH30Trim Labor196.00
5LH80Staple Labor67.00
6ST10Masking Labor45.00
7ST30Brown Labor1,010.00
8ST50Finish Labor938.00
9ST70Water34.00
10SC10Scaffold Erect256.00
11SC20Scaffold Down86.00
12ST90Cleanup22.00
1387/C222 ANY STREETLH10Lath Labor519.00
14LH30Trim Labor103.00
15LH80Staple Labor24.00
16ST10Masking Labor8.00
17ST30Brown Labor375.00
18ST50Finish Labor348.00
19ST70Water12.00
20SC10Scaffold Erect108.00
21SC20Scaffold Down36.00
LABOR


As you can see my attempt doesn't work. the info and Formula on Crew 4 sheet will extend to Col Q.


The data on LABOR sheet is variable (could be longer than 21 Rows) and is not consistent if you notice that info between Rows 3 and 12 is 10 items and between Rows 13 and 21 is 9 items. Other entries could be more or less items.

As I type this question, I'm not sure if a Formula (preferred) is the proper approach. I'm open for a macro but I would think it would have to be in the Worksheet Code Window since these two sheets would be copied to a New Workbook and sent to individuals to fill in the info on the Crew sheet.

Any help or guidance is much appreciated.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,504
Office Version
  1. 365
Platform
  1. Windows
{=INDEX(LABOR!$A$3:$E$21,MATCH(C5,C7&C8,),0)}

The MATCH function's format is as such:

MATCH(lookup_value,lookup_array,type)

You are using C5 as your only lookup value and under lookup array you are using C7&C8... the lookup array should be column A for the C5 and column C for the C7&C8...


Also, the first part of your INDEX function should be the array you want to return data from... I'm not sure what you're looking for... do you want to return the address? Or the description? That is the array you should enter in the first part of your INDEX formula... so for instance, if you wanted to find the description that matches both C5 in column A, and also matches C7&C8 under column C... the formula might look something like this:

=INDEX(LABOR!$D:$D,MATCH(C7&C8,IF(LABOR!$A:$A=C5,LABOR!$C:$C),0))

CTRL+SHIFT+ENTER



edit: actually I must add that this formula will only work if column A does not have all those blanks... you would have to arrange your sheet so that "87/45" is filled down all the way to row 12, and that "87/C" is filled down to row 21... etc etc... because the formula is looking for a value in that column...
 
Last edited:

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
Thanks for your reply,

I guess I didn't specify what I wanted to Return. I want to return the value that is in Col. E (Budget)

Unfortunately column A is populated by a macro that does not populate all the way down to the next change in that column.

I was hoping that I could accomplish this task without changing the macro that populates the Labor sheet.

Thank you for the education on the Index Match formula. I tried it with the appropriate Col for the return value (Col E), but of course it errors because of Col A not being populated.


Excel 2010
CDE
4TESTTESTTEST
587/4587/C87/45
68001A8001A8001A
7LHSTSC
8105020
91,100.00#N/A#N/A
Crew 4
Cell Formulas
RangeFormula
C9{=INDEX(LABOR!$E:$E,MATCH(C7&C8,IF(LABOR!$A:$A=C5,LABOR!$C:$C),0))}
D9{=INDEX(LABOR!$E:$E,MATCH(D7&D8,IF(LABOR!$A:$A=D5,LABOR!$C:$C),0))}
E9{=INDEX(LABOR!$E:$E,MATCH(E7&E8,IF(LABOR!$A:$A=E5,LABOR!$C:$C),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.


Is there another way to accomplish this so I don't have to change the macro that populates the Labor sheet?

The way that the Labor sheet is set up depends on other macros that does other things. So changing the Labor sheet is not an option unless I change a lot of other coding, which I really don't want to do.
 

pbt

Well-known Member
Joined
Oct 18, 2005
Messages
1,613
Okay, I rewrote my code to populate the Labor sheet to fill Col A. It now looks like this:

Excel 2010
ABCDE
1DRRC15013Robon Ranch-Denton
2Lot / BlockAddressCodeDescriptionBudget
333/4712505 RidgedaleLH10Lath Labor758.00
433/47LH30Trim Labor205.00
533/47LH80Staple Labor38.00
633/47ST10Masking Labor35.00
733/47ST30Brown Labor610.00
833/47ST50Finish Labor262.00
933/47SC10Scaffold Erect54.00
1033/47SC20Scaffold Down18.00
1132/4712501 RidgedaleLH10Lath Labor753.00
1232/47LH30Trim Labor178.00
1332/47LH80Staple Labor37.00
1432/47ST10Masking Labor38.00
1532/47ST30Brown Labor601.00
1632/47ST50Finish Labor258.00
1732/47SC10Scaffold Erect54.00
1832/47SC20Scaffold Down18.00
LABOR


I have also adjusted my formula a bit on the Crew 4 sheet ROW 9:

={IFERROR(INDEX(LABOR!$E:$E,MATCH(C7&C8,IF(LABOR!$A:$A=C5,LABOR!$C:$C),0)),"")}


This all works just fine.

Is there a way to add in a 4th criteria. I need to check if the entry on the Crew Sheet Row 4 matches what is on the Labor Sheet cell A1.

I'm hoping that I don't have to change the layout of the LABOR sheet because that would require a lot more modification to existing code that makes the LABOR sheet. Which other code depends on the current set up.
 

svendiamond

Well-known Member
Joined
Jun 13, 2014
Messages
1,504
Office Version
  1. 365
Platform
  1. Windows
I need to check if the entry on the Crew Sheet Row 4 matches what is on the Labor Sheet cell A1.

I'm assuming this would mean cells C4, D4, and E4 in your first example of the crew sheet where you originally wrote TEST... in that case, you could just add an IF statement at the beginning, like:


=IFERROR(IF(C4=LABOR!$A$1,INDEX(LABOR!$E:$E,MATCH(C7&C8,IF(LABOR!$A:$A=C5,LABOR!$C:$C),0)),""),"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,074
Messages
5,835,229
Members
430,349
Latest member
Gregori

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
Top