Index Match or Find Function Help

[kylenusser]

Hello. I am trying to compile a database and use some function to match the number of calls ran by date.

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1/1/18[/TD]
[TD][/TD]
[TD]1/2/18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Facility A[/TD]
[TD]15[/TD]
[TD]Facility A[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Facility B[/TD]
[TD]20
[/TD]
[TD]Facility B[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Facility C[/TD]
[TD]30[/TD]
[TD]Facility C[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Facility D[/TD]
[TD]20[/TD]
[TD]Facility D[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Facility E[/TD]
[TD]8[/TD]
[TD]Facility E[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Facility F[/TD]
[TD]9[/TD]
[TD]Facility F[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Facility G[/TD]
[TD]5[/TD]
[TD]Facility G[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Facility H[/TD]
[TD]8[/TD]
[TD]Facility H[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Facility I[/TD]
[TD]10[/TD]
[TD]Facility I[/TD]
[TD]20[/TD]
[/TR]
</tbody>[/TABLE]


Above is a database of the first two days. I am trying to compile a sheet that has Facility A-F in column A and in Column B-Etc is each date from 1/1/18-12/31-18. I am trying to see if there is a function that will allow me to search the table above for say Facility A on 1/1/18. I want to set it to be able to pull any day, as the table will ultimately extend to 12/31/18.

Does anyone know of any functions that can do this?

 

[yky]

=HLOOKUP(date,B1:NC2,2)

"date" is a serial number. It is 43110 for 10/1/18.

 

[kylenusser]

Maybe I wasnt clear enough. That does return a value.

On Sheet 1, I have as follows:

Column A Column B Column C
1/1/18 1/2/18
Facility A ?
Facility B ?
Facility C etc ?

On Sheet 2 I have every Facility in the database.

Column A Column B Column C Column D
1/1/18 Trips 1/2/18 Trips

Facility A 5 Facility A 10
....... ......
....... ......
Facility Z 15 Facility Z 18


As you can see Column A and C are the facilities and B and D are trips and this continues for each subsequent date.

Index(B:B,Match(A2,A:A,0)) auto filled to the next column would be Index(C:C,Match(A2,B:B,0)) which wouldnt match anything. I need Column B to read Index(D:D,Match(A2,C:C,0)) and skip two columns for every autofill

 

[yky]

You said you want to create a worksheet "that has Facility A-F in column A and in Column B-Etc is each date from 1/1/18-12/31-18". I don't understand why you need to repeat facilities every other column, like what you have now.

 

[kylenusser]

https://docs.google.com/spreadsheets/d/1DQS_xqtr9X9tj6JXVKyofT99GWq7QMVGeqYj0PzF81w/edit?usp=sharing

Maybe this google doc will make more sense. Do you see how I have to skip a column for the index match function to work? I want to find a way to drag that without having to skip a column every time

 

[Marcelo Branco]

Try this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td][/td][td]

01/01/2018​

[/td][td]

01/02/2018​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Facility A​

[/td][td]

5​

[/td][td]

4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Facility B​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Facility C​

[/td][td]

4​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Facility D​

[/td][td]

1​

[/td][td]

1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Facility E​

[/td][td]

6​

[/td][td]

7​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

Facility F​

[/td][td]

21​

[/td][td]

1​

[/td][/tr]
[/table]

Formula in B2 copied across and down
=INDEX(INDEX(Sheet2!$A:$ZZ,0,MATCH(B$1,Sheet2!$1:$1,0)+1),MATCH($A2,Sheet2!$A:$A,0))

Hope this helps.

M.

 

[kylenusser]

Try this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD]

01/01/2018​

[/TD]
[TD]

01/02/2018​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Facility A​

[/TD]
[TD]

5​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Facility B​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Facility C​

[/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

Facility D​

[/TD]
[TD]

1​

[/TD]
[TD]

1​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

Facility E​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

Facility F​

[/TD]
[TD]

21​

[/TD]
[TD]

1​

[/TD]
[/TR]
</tbody>[/TABLE]


Formula in B2 copied across and down
=INDEX(INDEX(Sheet2!$A:$ZZ,0,MATCH(B$1,Sheet2!$1:$1,0)+1),MATCH($A2,Sheet2!$A:$A,0))

Hope this helps.

M.

Youre a legend Thank you

 

[Marcelo Branco]

Disregard the above formula. Try this simpler formula

B2 copied across and down
=INDEX(Sheet2!$A:$ZZ,MATCH($A2,Sheet2!$A:$A,0),MATCH(B$1,Sheet2!$1:$1,0)+1)

M.

 

[kylenusser]

Quick Question. Say in Column C on Sheet 2 there is a Facility G, which isnt in Column A. How can that formula apply to that?

Disregard the above formula. Try this simpler formula

B2 copied across and down
=INDEX(Sheet2!$A:$ZZ,MATCH($A2,Sheet2!$A:$A,0),MATCH(B$1,Sheet2!$1:$1,0)+1)

M.

 

[Marcelo Branco]

Quick Question. Say in Column C on Sheet 2 there is a Facility G, which isnt in Column A. How can that formula apply to that?

Not sure i understand what you are looking for.

See if this works
B2 copied across and down
=IFERROR(INDEX(INDEX(Sheet2!$A:$ZZ,0,MATCH(B$1,Sheet2!$1:$1,0)+1),MATCH($A2,INDEX(Sheet2!$A:$ZZ,0,MATCH(B$1,Sheet2!$1:$1,0)),0)),"")

M.