Index and Match multiple columns and find answer for 3rd in Excel

[jmcconville18]

This may just be a simple answer that I'm looking too much into.

On my first sheet I have 5 columns of data from where I want to pull information.

[A1]"Pickup Date" [B1]"Truck #" [C1] "Trailer #" [D1] "Customer Name"

On the second sheet I have three of the same columns

[A1] "Pickup Date" [B1]"Truck Number" [C1]"Customer Name"

I'm Wondering if I have supplied the "Pickup Date" and "Truck Number" on the second sheet if I can grab the corresponding "Customer Name" from sheet 1 and paste it into C2 on sheet 2.

Here's the formula I have tried in C2 under customer name. I know it's not in correct format but I need both the Pickup Date and Truck Number to match and paste the

customer name=INDEX(Table1,( MATCH(A1,Table1[Pick Up Date],0)&MATCH(B2,Table1[Truck #],0)),4)
Thanks For your help

[Glenn Kennedy]

This will work, but must be entered as an ARRAY FORMULA. This is done NOT by using ENTER, but by using CONTROL-SHIFT-ENTER together.

Please Login or Register  to view this content.

OK?

[sktneer]

Another one.....
Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of just Enter.

On Sheet2, In C2

Please Login or Register  to view this content.

Adjust the ranges in the formula as per your sheet data.

[zbor]

Or this non-array (based on sktneer solution).

Please Login or Register  to view this content.

[sktneer]

@ zbor
I completely forgot that the array formula, suggested me, could easily be converted into a non-array formula. Thanks for giving that solution.

[Tony Valko]

Here's another one...

Sheet1

	A	B	C	D
1	Date	Truck	Trailer	Customer
2	5/27/2014	4	1	A
3	5/27/2014	2	3	C
4	5/29/2014	5	2	A
5	5/31/2014	1	2	B
6	6/1/2014	3	2	C
7	6/5/2014	5	4	B
8	6/5/2014	2	2	C
9	6/8/2014	2	5	A
10	6/10/2014	1	3	B

Sheet2

	A	B	C
1	Date	Truck	Customer
2	6/5/2014	5	B

This array formula** entered in C2:

=INDEX(Sheet1!D2:D10,MATCH(B2,IF(Sheet1!A2:A10=A2,Sheet1!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.

[jmcconville18]

Man, you guys are lifesavers. Much appreciated! Now my next project is, if there are multiple matches but different customers, to create another entry on sheet 2.

Thanks alot!

[Tony Valko]

Sheet1

	A	B	C	D
1	Date	Truck	Trailer	Customer
2	5/27/2014	4	1	A
3	5/27/2014	2	3	C
4	5/29/2014	5	2	A
5	5/31/2014	1	2	B
6	6/1/2014	3	2	C
7	6/5/2014	5	4	B
8	6/5/2014	5	4	C
9	6/8/2014	2	5	A
10	6/10/2014	1	3	B

Sheet2

	A	B	C
1	Date	Truck	Customer
2	6/5/2014	5	B
3			C
4

This array formula entered in C2:

=INDEX(Sheet1!D:D,SMALL(IF(Sheet1!A$2:A$10=A$2,IF(Sheet1!B$2:B$10=B$2,ROW(Sheet1!D$2:D$10))),ROWS(Sheet1!C$2:C2)))

** 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 down until you get blanks.

[jmcconville18]

I'm having trouble following the last one. My information from sheet 1 is in "Table 1." I have substituted the columns into table columns. EG "Sheet1!D:D" into "Table1[Customer]. Here's the code I used

=INDEX(Table1[Customer],SMALL(IF(Table1[Pick Up Date]=A$2,IF(Table1[Truck '#]=B$2,ROW(Table1[Customer]))),ROWS(Table1[Trailer '#])))

I'm getting a #NUM! error when I input this.

Also I'm confused as to why you referenced the Trailer number from sheet1

your last segment of the code is "ROWS(Sheet1!C$2:C2)" which would allude to the trailer number on sheet 1. correct?

could this be a typo?

[Tony Valko]

your last segment of the code is "ROWS(Sheet1!C$2:C2)" which would allude to the trailer number on sheet 1. correct?

No.

I hate structured references!

Can you post a SMALL sample file?

A SMALL file will have about 20 rows and just a few columns worth of data.

[jmcconville18]

Asset Tracking Sheet Test.xlsx

"Trucks Accounted for" sheet is where I want the customer row added

[Tony Valko]

Here is what your summary table looks like:

Data Range

	A	B	C	D	E	F
1	Date	Truck Number	Utilization	Customer	Code Type	Comments
2	6/1/2014	TT 01	100%	Weatherford
3	6/1/2014	TT 02	100%	Halliburton
4	6/1/2014	TT 03	100%	Weatherford
5	6/1/2014	TT 04	100%	Weatherford

When there are multiple customers per date/truck number, where should the other customer names go? I hope you don't say: in the same cell separated by a comma.

[jmcconville18]

I would like the customer names to go in a completely different row. possibly populated with the date and truck number

Date	Truck Number	Utilization	Customer	Code Type
6/1/2014	TT 01	100%	Weatherford
6/1/2014	TT 02	100%	Halliburton
6/1/2014	TT 02		USWS

[XOR LX]

I hate structured references!

Likewise. Yet the number of websites out there that rave about them is not insignificant...(Have you tried working with absolute/relative structured references?!)

Regards

[Glenn Kennedy]

Yes. And I have to look up the syntax every time. A nightmare

[Tony Valko]

I would like the customer names to go in a completely different row. possibly populated with the date and truck number

Hmmm...

I'm wondering if you'd be better off with a pivot table.

In order to do what you want we'd have to build the entire summary table with formulas. This seems like a duplication of effort since you already have all this data on the Asset Tracking Sheet. Is the Trucks Accounted For sheet supposed to be just a specific subset of the data from the Asset Tracking Sheet?

[Tony Valko]

Have you tried working with absolute/relative structured references?

Yes, it's even worse!

I used to use an INDIRECT construct but then I learned about this:

TableName[[RangeName]:[RangeName]]

I guess that's better than using INDIRECT but it could lead to very long formulas!

The only good thing about tables and structured referencing is that the references to the table are automatically dynamic.

As far as being easier to read and interpret... nope, just doesn't work for me.

When I opened the OP's file the first thing I had to do was go looking for all the tables and named ranges.

If I see a formula like this:

=INDEX(Sheet1!D:D,SMALL(IF(Sheet1!A$2:A$10=A$2,IF(Sheet1!B$2:B$10=B$2,ROW(Sheet1!D$2:D$10))),ROWS(Sheet1!C$2:C2)))

I instantly know where everything is located!

[jmcconville18]

This seems like a duplication of effort since you already have all this data on the Asset Tracking Sheet. Is the Trucks Accounted For sheet supposed to be just a specific subset of the data from the Asset Tracking Sheet?

Yes exactly. The data is inputted into the Asset Tracking sheet and the idea is to only input the data once and use formulas to populate the Trucks accounted for. A month could hold 500 entries so its pivotal to have referential integrity. My thoughts are to ultimately use the truck utilization report and put it into a pivot chart to visualize which trucks were used on different days and being able to sort by customer or time period.

[Tony Valko]

Ok, what criteria is used to determine the data extract? Is it the date?

Also, in the sample file the data on the Trucks Accounted For sheet is sorted by truck number.

If we have to use formulas to do the extraction we might not be able do it with the truck numbers sorted like that. It might be possible but it'd be pretty complicated.

[jmcconville18]

The combination of the date and the truck number. So even if a truck is not being used on a certain date it should still be accounted for and display an error saying "Not Utilized"

[Tony Valko]

I'm not sure we're understanding each other but here's what I've come up with.

On some sheet:

A2 = some date
B2 = some truck number

To list the customers for those criteria this array formula** entered in C2:

=IF(OR(C1={"","Not Utilized"}),"",IF(COUNTIFS(Table1[Pick Up Date],A$2,Table1[Truck '#],B$2)=0,"Not Utilized",IFERROR(INDEX(Table1[Customer],SMALL(IF(Table1[Pick Up Date]=A$2,IF(Table1[Truck '#]=B$2,ROW(Table1[Customer])-MIN(ROW(Table1[Customer]))+1)),ROWS(C$2:C2))),"")))

** 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 down until you get blanks.

[jmcconville18]

This works to grab the customer information but it does not create a new row when the data has changed from the tracking sheet. I have attached two images to show visually what it should look like.

This is the first sheet where data is inputted
Tracking Sheet.jpg
as you can see there are two customers for the "TT 01" and "TT 02" in the same date

This is what the Trucks Accounted for should look like
Trucks Accounted For.jpg
Next to the arrows are the rows that should be automatically inserted once the data is put in from the first sheet.

in essence once the formula recognizes there are two customers for the same truck and date it will automatically create a new row and insert the next customer under it.

I hope this helps

[jmcconville18]

Would it be possible to add a a column to the right to display the second customers name automatically?

[Tony Valko]

Like this...

Data Range

	A	B	C	D	E
1	Date	Truck Num	Customer(s)	------	------
2	6/1/2014	TT 01	Weatherford	USWS

This array formula** entered in C2:

=IF(OR(B2={"","Not Utilized"}),"",IF(COUNTIFS(Table1[Pick Up Date],$A2,Table1[Truck '#],$B2)=0,"Not Utilized",IFERROR(INDEX(Table1[Customer],SMALL(IF(Table1[Pick Up Date]=$A2,IF(Table1[Truck '#]=$B2,ROW(Table1[Customer])-MIN(ROW(Table1[Customer]))+1)),COLUMNS($C2:C2))),"")))

** 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 until you get blanks.

[jmcconville18]

Like this...

Data Range

	A	B	C	D	E
1	Date	Truck Num	Customer(s)	------	------
2	6/1/2014	TT 01	Weatherford	USWS

This array formula** entered in C2:

=IF(OR(B2={"","Not Utilized"}),"",IF(COUNTIFS(Table1[Pick Up Date],$A2,Table1[Truck '#],$B2)=0,"Not Utilized",IFERROR(INDEX(Table1[Customer],SMALL(IF(Table1[Pick Up Date]=$A2,IF(Table1[Truck '#]=$B2,ROW(Table1[Customer])-MIN(ROW(Table1[Customer]))+1)),COLUMNS($C2:C2))),"")))

** 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 until you get blanks.

This Works Perfectly! thanks. I have another question and it's kindov tedious but is there anyway to group multiple customer entries? So say that I have three customer entries

A	B	C	D	E	F
	Date	Truck #	Customer(s)
1	6/1/2014	TT 01	USWS	USWS	Weatherford

Converted to

A	B	C	D	E	F
	Date	Truck #	Customer(s)
1	6/1/2014	TT 01	USWS	Halliburton

Thanks

[Tony Valko]

Shouldn't that be:

A	B	C	D	E	F
	Date	Truck #	Customer(s)
1	6/1/2014	TT 01	USWS	Weatherford

In other words, you just want the unique customers?

[jmcconville18]

Yes sorry about the typo

[Tony Valko]

We can probably do that.

However, I'll be offline for several hours so I won't get to it until late tonight or tomorrow.

[Tony Valko]

Try this...

A2 = some date
B2 = some truck number

This array formula** entered in C2:

=IF(OR(B2={"","Not Utilized"}),"",IF(COUNTIFS(Table1[Pick Up Date],$A2,Table1[Truck '#],$B2)=0,"Not Utilized",IFERROR(INDEX(Table1[Customer],MATCH(0,IF(Table1[Pick Up Date]=$A2,IF(Table1[Truck '#]=$B2,COUNTIF($B2:B2,Table1[Customer]))),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 until you get blanks.

[jmcconville18]

Nailed it! Thank you

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.