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:

1/1/18		1/2/18
Facility A	15	Facility A	25
Facility B	20	Facility B	15
Facility C	30	Facility C	15
Facility D	20	Facility D	10
Facility E	8	Facility E	7
Facility F	9	Facility F	10
Facility G	5	Facility G	6
Facility H	8	Facility H	9
Facility I	10	Facility I	20

<tbody>
</tbody>

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

	A​	B​	C​
1​		01/01/2018​	01/02/2018​
2​	Facility A​	5​	4​
3​	Facility B​	1​	1​
4​	Facility C​	4​	1​
5​	Facility D​	1​	1​
6​	Facility E​	6​	7​
7​	Facility F​	21​	1​

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

	A​	B​	C​
1​		01/01/2018​	01/02/2018​
2​	Facility A​	5​	4​
3​	Facility B​	1​	1​
4​	Facility C​	4​	1​
5​	Facility D​	1​	1​
6​	Facility E​	6​	7​
7​	Facility F​	21​	1​

<tbody>
</tbody>

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.