Index Match with 3 Criteria

[pbt]

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
	C	D	E
4	TEST	TEST	TEST
5	87/45	87/C	87/45
6	8001A	8001A	8001A
7	LH	ST	SC
8	10	50	20
9	#VALUE!	#VALUE!	#VALUE!
Crew 4

Cell Formulas
Range	Formula
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
	A	B	C	D	E
2	Lot / Block	Address	Code	Description	Budget
3	87/45	222 ANY STREET	LH10	Lath Labor	1,100.00
4			LH30	Trim Labor	196.00
5			LH80	Staple Labor	67.00
6			ST10	Masking Labor	45.00
7			ST30	Brown Labor	1,010.00
8			ST50	Finish Labor	938.00
9			ST70	Water	34.00
10			SC10	Scaffold Erect	256.00
11			SC20	Scaffold Down	86.00
12			ST90	Cleanup	22.00
13	87/C	222 ANY STREET	LH10	Lath Labor	519.00
14			LH30	Trim Labor	103.00
15			LH80	Staple Labor	24.00
16			ST10	Masking Labor	8.00
17			ST30	Brown Labor	375.00
18			ST50	Finish Labor	348.00
19			ST70	Water	12.00
20			SC10	Scaffold Erect	108.00
21			SC20	Scaffold Down	36.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.

 

[svendiamond]

{=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...

 

[pbt]

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
	C	D	E
4	TEST	TEST	TEST
5	87/45	87/C	87/45
6	8001A	8001A	8001A
7	LH	ST	SC
8	10	50	20
9	1,100.00	#N/A	#N/A
Crew 4

Cell Formulas
Range	Formula
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]

Okay, I rewrote my code to populate the Labor sheet to fill Col A. It now looks like this:

Excel 2010
	A	B	C	D	E
1	DRRC15013	Robon Ranch-Denton
2	Lot / Block	Address	Code	Description	Budget
3	33/47	12505 Ridgedale	LH10	Lath Labor	758.00
4	33/47		LH30	Trim Labor	205.00
5	33/47		LH80	Staple Labor	38.00
6	33/47		ST10	Masking Labor	35.00
7	33/47		ST30	Brown Labor	610.00
8	33/47		ST50	Finish Labor	262.00
9	33/47		SC10	Scaffold Erect	54.00
10	33/47		SC20	Scaffold Down	18.00
11	32/47	12501 Ridgedale	LH10	Lath Labor	753.00
12	32/47		LH30	Trim Labor	178.00
13	32/47		LH80	Staple Labor	37.00
14	32/47		ST10	Masking Labor	38.00
15	32/47		ST30	Brown Labor	601.00
16	32/47		ST50	Finish Labor	258.00
17	32/47		SC10	Scaffold Erect	54.00
18	32/47		SC20	Scaffold Down	18.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]

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)),""),"")