Using Index Match Funtion

[rehwright]

Hi, I'm trying to use the index match function to find the result where the source (in this case the account) could be in columns A through E, and the month is in row 1.

Here is my formula, I know this is not correct, anyone know how to write this correctly? Any help would be greatly appreciated.

=IFERROR(INDEX(BalSheetbyMo!1:100,MATCH('BS Table'!A11,BalSheetbyMo!A:E,0),MATCH('BS Table'!C1,BalSheetbyMo!1:1,0)),0)

 

[DanteAmor]

The account is only in column A? or can it be in any cell of columns A -E?
The months in which column they begin in the F?
The account only appears once?

 

[rehwright]

The account could be in Columns A,B,C,D or E, the month is always in row 1

 

[DanteAmor]

The months in which column they begin in the F?
The account only appears once?

 

[DanteAmor]

The months in which column they begin in the F?
The account only appears once?
You can give an example of what you have in these cells
BS Table'!A11,
BS Table'!C1

 

[rehwright]

I'm trying to do a lookup at the intersection of the account and the month. The account numbers can be in any of the Columns from A to E, the month is in row 1. If the account is in column A, then the formula below works:

=IFERROR(INDEX(BalSheetbyMo!1:100,MATCH('BS Table'!A11,BalSheetbyMo!A:A,0),MATCH('BS Table'!C1,BalSheetbyMo!1:1,0)),0)

I need to know how to write the formula if it is in any of the other columns?

 

[DanteAmor]

Try this


=OFFSET(BalSheetbyMo!$A$1,SUMPRODUCT((BalSheetbyMo!$A$2:$E$1000=A11)*ROW(BalSheetbyMo!$A$2:$E$1000))-1,MATCH(C1,BalSheetbyMo!$1:$1)-1)

 

[DanteAmor]

To better understand your requirements it is always important to have examples with real data. The explanation would be more practical with examples.

 

[rehwright]

Yes, excellent help, thank you very much!!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.