INDEX/MATCH on 4 sets of criteria

hiker8117 · Mar 26, 2018

I am attempting to pull in select data from a Project Worksheet1 into an Expenditure Worksheet2 within the same workbook using 4 set of criteria in the Expenditure Worksheet2

The 4 sets of Match criteria found in the Expenditure Worksheet2 is as follows:

FY-18 Project A1 Labor Dollars WRKLD

I have tried numerous times with Index/Match only to get zero results.

I want to pull in labor dollars for a specific project in a fiscal year under WRKLD.

Any help would be appreciated.

Thank you

Gene Haines

jorismoerings · Mar 27, 2018

Hi,

Probably the copying of your data went wrong and without it your question is difficult to answer.

Joe4 · Mar 27, 2018

Welcome to the Board!

There are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.

Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

umplebyuk · Mar 27, 2018

Use Offset & Match as an array formula

Formula would be =offset("Header Row of result you want to bring in (e.g LookupSheetA1)",match(A1&B1&C1&D1,LookupSheetA1:A100&LookupSheetB1:B100&LookupSheetC1:C100&LookupSheetD1:D100,0)-1,0,1,1) THEN HOLD SHIFT & CONTROL DOWN AND HIT ENTER (This stores the formula as an array formula)

hiker8117 · Mar 27, 2018

jorismoerings said:
Hi,

Probably the copying of your data went wrong and without it your question is difficult to answer.

Example: Trying to bring in the FY-18 Project A1 Lab Dollars under Category WRKLD into Expenditure Worksheet 2 using the Match criteria in Expenditure Work Sheet 2 into the cell New Fy-18 Lab Cst. It should be $45789-618=$45,171. The Index/Match Function that I use is at the end of this info. It gives me an error message of "too many auguments"

Hope this is not too confusing as I am new to thsi forum.

Thank you

Gene Haines

		Project Workheet1

		FY-18	FY-19
Project	Category	Lab Dollars	Mat Dollars	Total Cost to Cmplt	Lab Dollars	Mat Dollars	Total Cost to Cmplt
A1	Reservation	$0	$30,000	$30,000	$0	$0	$0
	WRKLD	$45,789	$2,500	$48,289	$31,587	$1,298	$32,885
A2	Reservation	$25,000	$0	$25,000	$0	$0	$0
	WRKLD	$32,678	$3,450	$36,128	$21,589	$1,362	$22,951
A3	Reservation	$45,000	$0	$45,000	$0	$0	$0
	WRKLD	$56,743	$4,000	$60,743	$41,578	$4,000	$45,578
Total		$205,210	$39,950	$245,160	$94,754	$6,660	$101,414

		Expenditure Work Sheet2			index/match	index/match
Project	Current Labor	Prior Mnth Labor	Current Material	Prior Mnth Material	Difference in Lab	Difference in Mat	New FY-18 Lab Cst	New FY-18 Lab Mat	Total Funds Avail
A1	1896	1278	125	75	618	50	$45,171	$2,450	$51,000
A2	2578	1970	265	163	608	102	$32,070	$3,348	$36,000
A3	3256	2478	369	198	778	171	$55,965	$3,829	$65,000
Total

Match	Match	Match	Match	Match
Year	Labor	Material	Cost	Cat
FY-18	Lab Dollars	Mat	Cst Cplt	WRKLD

INDEX($C$5:$H11,MATCH($A$17:$A$19,$A$4:$A$11),MATCH($A$25,$C$3:$H$3),MATCH($B$25,$C$4:$H$4),MATCH($E$25,$B$5:$H$10)0)))

Dryver14 · Mar 27, 2018

INDEX($C$5:$H11,MATCH($A$17:$A$19,$A$4:$A$11),MATCH($A$25,$C$3:$H$3),MATCH($B$25,$C$4:$H$4),MATCH($E$25,$B$5:$H$10)0)))

This needs to be one cell Probably A19 but it's hard to work out.

Trying to understand this, as you have too many matches over all

hiker8117 · Mar 27, 2018

Trying to retrieve in Project Worksheet1 the following:

Project A1 FY-18 Lab Dollars under Category WRKLD which is $45,789

and post this amount into Expenditure Work Sheet2 into Project A1 New FY-18 Lab Cst column.

The index/match function if it worked would then have an additional formula that would subtract this amount from the Difference in Lab column in the Expenditure Work Sheet2 to give me a New FY-18 Lab Cst.

Here is an explanation of the segments of the index/match function:

INDEX($C$5:$H11) are the dollars in the Project Worksheet1

MATCH($A$17:$A$19,$A$4:$A$11), A17:A19 is the Project Name in Work Sheet2 and A4:A11 is Project Name in Worksheet1. I guess the Match cell should be A17.

MATCH($A$25, $C$3:$H$3), A25 is the Year in Work Sheet2 and C3:H3 is the Year in Worksheet1.

MATCH($B$25,$C$4:$H$4), B25 is the Labor Dollars in Work Sheet2 and C4:H4 is the Cat: Lab Dollars or Mat in Worksheet1

MATCH($E$25,$B$5:$B$10), $E$25 is the Cat WRKLD in Worksheet2 and B5:B10 is the Cat in Worksheet1 Although I have in the post $B$5:$H$10

I hope I have made myself a little bit clearer.

Thank you

Gene Haines

hiker8117 · Mar 27, 2018

Thank you. Will give this a try

Regards
Gene

jorismoerings · Mar 29, 2018

other option:

Book1

A

B

C

D

E

F

G

H

I

J

1

Project Workheet1

2

3

FY-18

FY-19

4

Project

Cell Formulas
Range	Formula
H15	=OFFSET($A$3,MATCH(A15,$A$5:$A$10,0)+MATCH($E$22,$B$5:$B$6,0),MATCH($A$22,$C$3:$H$3,0)+MATCH($B$22,$C$4:$D$4,0))
I15	=OFFSET($A$3,MATCH(A15,$A$5:$A$10,0)+MATCH($E$22,$B$5:$B$6,0),MATCH($A$22,$C$3:$H$3,0)+MATCH($C$22,$C$4:$D$4,0))

INDEX/MATCH on 4 sets of criteria

hiker8117

New Member

Excel Facts

jorismoerings

Well-known Member

Joe4

MrExcel MVP, Junior Admin

umplebyuk

New Member

hiker8117

New Member

Dryver14

Well-known Member

hiker8117

New Member

hiker8117

New Member

jorismoerings

Well-known Member

hiker8117

New Member

Similar threads

Forum statistics

Share this page

INDEX/MATCH on 4 sets of criteria

New Member

Excel Facts

Well-known Member

MrExcel MVP, Junior Admin

New Member

New Member

Well-known Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock