Data Validation, Index/Match, Offset Formula Help

[chrono2483]

I have a spreadsheet where I am working with DV, Index/Match and Offset. I am running into a problem, in that the formula is correct, and seems to display for some intervals, but not for others. Can someone help spot where I may be going wrong?

The user will be able to select the time interval. This will display the selected interval, as well as the next interval. Also the team can be selected. The inconsistency is that when I select the time, some intervals will appear, and some wont (ex. 4:00pm will display, but 5:00pm will not, 11:00am & 1200pm will display, but 12:00pm will display and 1:00pm

The formula I am working with is the following:

=IFERROR(IF($G$8="Help Eng",OFFSET(INDEX(Paste!$B:$B,MATCH($C$12,Paste!$B:$B,0)),2,0,,),IF($G$8="Help Fre",OFFSET(INDEX(Paste!$C:$C,MATCH($C$12,Paste!$C:$C,0)),2,0,,),"")),"")

 

[konew1]

What is being returned by the Index/match component?

 

[chrono2483]

Hi konew1,

The Index/Match component is returning data based on what the user selects. There are 2 sheets, a 'Paste' and a 'View'. On the Paste sheet, raw data is pasted, which looks like the below (Col A/B).

The View sheet, has 2 DV lists. One for Hourly time (9:00am, 10:00am, 11:00am, 12:00pm, etc) and the other is for Group (Help Eng, Help Fre, etc).

Below the 2 lists, is a chart (similar to below). The time frames display, based on the user selection. Ex: If they select 11:00am, it will display that in the chart, but also display 12:00pm below. Then, based on the group the user selects, it will return the data from the Paste sheet.

So, the Index/Match component is searching for the TIME selected on 'View' sheet, finding it on the 'Paste' sheet. Once found, it will OFFSET to return the desired cell of data.

[TABLE="class: grid, width: 128"]
<tbody>[TR]
[TD="align: right"]Help Eng
[/TD]
[TD="align: right"]Help Fre
[/TD]
[/TR]
[TR]
[TD="align: right"]11:00 AM
[/TD]
[TD="align: right"]4:00 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]100.00%[/TD]
[TD="align: right"]100.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0:00:00[/TD]
[TD="align: right"]0:00:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.00%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12:00 PM
[/TD]
[TD="align: right"]5:00 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 830"]
<colgroup><col><col span="3"><col><col><col span="4"><col span="2"></colgroup><tbody>[TR]
[TD]11:00 AM
[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]0.00%[/TD]
[TD]2[/TD]
[TD]100.00%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.00%[/TD]
[/TR]
[TR]
[TD]12:00 PM[/TD]
[TD]15[/TD]
[TD]64[/TD]
[TD]23.40%[/TD]
[TD]49[/TD]
[TD]76.60%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]100.00%[/TD]
[/TR]
</tbody>[/TABLE]


However, some time frames are not returning data.

[TABLE="class: grid, width: 830"]
<colgroup><col><col span="3"><col><col><col span="4"><col span="2"></colgroup><tbody>[TR]
[TD]12:00 PM
[/TD]
[TD]15[/TD]
[TD]64[/TD]
[TD]23.40%[/TD]
[TD]49[/TD]
[TD]76.60%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]15[/TD]
[TD]100.00%[/TD]
[/TR]
[TR]
[TD]1:00 PM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[chrono2483]

Looks like the issue, is that when the raw data is pasted, the time intervals (9:00am, 10:00am, 11:00am, etc) paste with a space before and after.

Is there a way to use index/match, so that it will still find the time intervals, regardless of them having a space before and after?

 

[konew1]

Try using TRIM function to remove leading and trailing spaces.
With helper cells if you have pasted raw data into A1 then in B1=TRIM(A1) and have your massive formula refer to B1.
You might be able to put the TRIM into your existing formula, but I don't understand it clearly enough to propose where.

 

[chrono2483]

Hi konew1, I tried using a helper cell, but no such luck. Is there a way to include a wildcard into my index/match formula, to consider the intervals where there is a space added?

This is the formula I am working with:

=IFERROR(IF($G$8="Elec Eng",OFFSET(INDEX('Paste'!$B:$B,MATCH($C$13,'Paste'!$B:$B,0)),2,0,,),IF($G$8="Elec Fre",OFFSET(INDEX('Paste'!$C:$C,MATCH($C$13,'Paste'!$C:$C,0)),2,0,,),IF($G$8="Home Eng",OFFSET(INDEX('Paste'!$E:$E,MATCH($C$13,'Paste'!$E:$E,0)),2,0,,),IF($G$8="Home Fre",OFFSET(INDEX('Paste'!$D:$D,MATCH($C$13,'Paste'!$D:$D,0)),2,0,,),IF($G$8="Home Care Eng",OFFSET(INDEX('Paste'!$F:$F,MATCH($C$13,'Paste'!$F:$F,0)),2,0,,),IF($G$8="Home Care Fre",OFFSET(INDEX('Paste'!$G:$G,MATCH($C$13,'Paste'!$G:$G,0)),2,0,,),IF($G$8="Support Eng",OFFSET(INDEX(' Paste'!$H:$H,MATCH($C$13,'Paste'!$H:$H,0)),2,0,,),IF($G$8="Support Fre",OFFSET(INDEX('Paste'!$I:$I,MATCH($C$13,'Paste'!$I:$I,0)),2,0,,),"")))))))),"")

$C$13/$C$14 = the time intervals selected from the DV List

 

[konew1]

Here is a check if TRIm is working for you
Say that the data that has extra spaces is in A1:A10
In B1 enter formula B1=LEN(A1) and copy down over B1:B10
In C1 formula C1=TRIM(A1) and copy down
In D1=LEN(C1) and copy
In E1=B1-D1 and copy down
If the result in column E are all zero then trim isn't working, which means you don't have leading spaces. Your problem is something else.
If the result in E:E is some positive numbers then modify your formula to refer to column C instead of column A.

If that doesn't work then you need to help the helpers on this forum. Your formula is complex and the readers don't have full understanding of what you are doing.
Remove all parts of the formula that are not a problem. You have 8 levels of IF. Identify the action if true that fails and post just that problem section of your formula