index match

[Holsum]

I am very new to the index match function. I am trying to pull a particular cell in a specific row of data that matches a certain criteria.

I am pulling summarized data from an large excel for financial statements.

The formula I have is: =INDEX(bs!$AF$3:$AF$300,MATCH('Irish Test'!L4,bs!$C$3:$C$300,0))

I am wondering if instead of using C3:C300 if I can use a range of columns more like A3:G300

A	B	C	D	E	F	G
ASSETS
	Current Assets
		Checking/Savings
			102-00C · WASHINGTON FEDERAL - C
			102-00E · WASHINGTON FEDERAL - E
			102-00H · WASHINGTON FEDERAL - H
			102-00I · WASHINGTON FEDERAL - I
			103-00E · VANGUARD TAX EXEMPT M/M - E
			103-00H · CASH - VANGUARD TAX EXMPT M/M
			103-00I · VANGUARD TAX EXEMPT M/M - I

<tbody>
</tbody>

 

[crazydragon84]

no. match can't do that.

you can use a vba function to return a cell for you (or column/row number). I don't get what you are trying to do so its a bit hard to give you exact instruction.

you can insert the following code into vba editor. in whatever cell you can input formula =find_index_Row(Sheet Name, Text you are looking for) and it will return you the row number of that text (note it will only return first instance). Same thing can be done with Find_Index_Column. You can also do =Address(find_index_Row(Sheet Name, Text you are looking for),find_index_Col(Sheet Name, Text you are looking for))) and it will return the cell address. Hope that helps.

Function Find_Index_Row(Sh_Name As String, Find_Text As String)    
    Find_Index_Row = Sheets(Sh_Name).Cells.Find(what:=Find_Text, LookIn:=xlValues, Lookat:=xlWhole).Row
End Function


Function Find_Index_Col(Sh_Name As String, Find_Text As String)
    Find_Index_Col = Sheets(Sh_Name).Cells.Find(what:=Find_Text, LookIn:=xlValues, Lookat:=xlWhole).Column
End Function

 

[Holsum]

Thanks.

 

[AhoyNC]

There may be a way to use INDEX with a range of columns, but we need more info.
Will Irish Test'!L4 only show up once in the range A3:G300 or could it show up multiple times?

Post a small sample of data and what the result should be.
The example below uses a range of columns where the look up value Pizza is only shown once and returns the value in column A, but it can be amended to return multiple rows.

It is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G
1	ABC	Buns	Crust	Burger		Find	Pizza
2	DEF	Sauce	Pizza	Soup
3	GHI	Cheese	Pasta	Apple		Return from
4						Column A	DEF
Sheet

 

[Holsum]

ASSETS AF Current Assets Checking/Savings 102-00C · WASHINGTON FEDERAL - C 56,880.00 51,729.50 5,150.50 -50,545.74 107,425.74 102-00E · WASHINGTON FEDERAL - E 0.00 0.00 0.00 0.00 0.00 102-00H · WASHINGTON FEDERAL - H 0.00 0.00 0.00 0.00 0.00 102-00I · WASHINGTON FEDERAL - I 0.00 0.00 0.00 0.00 0.00 103-00E · VANGUARD TAX EXEMPT M/M - E 0.00 0.00 0.00 0.00 0.00 103-00H · CASH - VANGUARD TAX EXMPT M/M 0.00 0.00 0.00 0.00 0.00 103-00I · VANGUARD TAX EXEMPT M/M - I 0.00 0.00 0.00 0.00 0.00 103-10I · SCHWAB TAXABLE MM - I 0.00 0.00 0.00 0.00 0.00 103-50H · CASH - FORD TAXABLE MM 0.00 0.00 0.00 0.00 0.00 103-50I · FORD TAXABLE MM - I 0.00 0.00 0.00 0.00 0.00 103-60H · GOLDMAN SACHS - H 0.00 0.00 0.00 0.00 0.00 104-00C · STATE BANK OF CHILTON - C -796.61 10,850.14 -11,646.75 -1,388.42 591.81 <tbody> </tbody>

<tbody>
</tbody>

This is a direct export from our financial software. The formula I am currently using to find the cell I need is =INDEX(bs!$AF$3:$AF$300,MATCH('Irish Test'!L4,bs!$C$3:$C$300,0))

Yes the item in L4 is only found once in this table.

I would like to use a range of columns for the match portion of the formula so that I do not have to know exactly which column L4 actually occurs in my main spreadsheet. I am trying to pull the data exactly how a client is used to seeing it instead of making them get used to our software.

I would like a formula to search columns A through G for a unique item such as account 102-00C and return the value that is in column AF. So a formula that would look for 102-00C and return value 5150.50 from column AF

 

[crazydragon84]

then you don't need to select a range of column.

all you need is index(AF:AF,MATCH('Irish Test'!L4,C:C,0)). I assume C is where your account numbers/descriptions are?

 

[AhoyNC]

I have made some assumptions here.
-1 That the 103-00C is not in a cell by itself, but it is part of 102-00C · WASHINGTON FEDERAL - C. If that's wrong we can simplify the formula some by removing the SEARCH function.
-2 When you down load the data it can be put in any column A-G.

You will need to change the ranges to match your data.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	AC	AD	AE	AF	AG	AH
1	*	Find	103-00C	*	*	*	*	*	*	*	*	*	*
2	*	Return from AF	5150.50	*	*	*	*	*	*	*	*	*	*
3	*	*	102-00C WASHINGTON FEDERAL - C	*	*	*	*	*	56,880.00	51,729.50	5,150.50	-50,545.74	107,425.74
4	*	*	102-00E WASHINGTON FEDERAL - E	*	*	*	*	*	0	0	0	0	0
5	*	*	102-00H WASHINGTON FEDERAL - H	*	*	*	*	*	0	0	0	0	0
6	*	*	102-00I WASHINGTON FEDERAL - I	*	*	*	*	*	0	0	0	0	0
7	*	*	*	103-00E VANGUARD TAX EXEMPT M/M - E	*	*	*	*	0	0	4,500.00	0	0
8	*	*	103-00H CASH - VANGUARD TAX EXMPT M/M	*	*	*	*	*	0	0	0	0	0
9	*	*	103-00I VANGUARD TAX EXEMPT M/M - I	*	*	*	*	*	0	0	12	0	0
10	*	*	103-10I SCHWAB TAXABLE MM - I	*	*	*	*	*	0	0	14	0	0
11	*	*	103-50H CASH - FORD TAXABLE MM	*	*	*	*	*	0	0	6000	0	0
12	*	*	103-50I FORD TAXABLE MM - I	*	*	*	*	*	0	0	0	0	0
13	*	*	103-60H GOLDMAN SACHS - H	*	*	*	*	*	0	0	0	0	0
14	*	*	104-00C STATE BANK OF CHILTON - C	*	*	*	*	*	-796.61	10,850.14	-11,646.75	-1,388.42	591.81
Sheet

 

[Holsum]

The problem is that they are not all in column C. I have account numbers in columns A thru G.

 

[AhoyNC]

The formula I posted will find the acct.# in any of the columns listed (A-G).
See below.
Again it is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	AC	AD	AE	AF	AG	AH
1		Find	102-00H
2		Return from AF	6580.00
3			102-00C WASHINGTON FEDERAL - C				56,880.00	51,729.50	5,150.50	-50,545.74	107,425.74
4			102-00E WASHINGTON FEDERAL - E				0	0	0	0	0
5					102-00H WASHINGTON FEDERAL - H		0	0	6,580.00	0	0
6			102-00I WASHINGTON FEDERAL - I				0	0	0	0	0
7				103-00E VANGUARD TAX EXEMPT M/M - E			0	0	4,500.00	0	0
8			103-00H CASH - VANGUARD TAX EXMPT M/M				0	0	0	0	0
9			103-00I VANGUARD TAX EXEMPT M/M - I				0	0	12	0	0
10			103-10I SCHWAB TAXABLE MM - I				0	0	14	0	0
11				103-50H CASH - FORD TAXABLE MM			0	0	6000	0	0
12			103-50I FORD TAXABLE MM - I				0	0	0	0	0
13			103-60H GOLDMAN SACHS - H				0	0	0	0	0
14			104-00C STATE BANK OF CHILTON - C				-796.61	10,850.14	-11,646.75	-1,388.42	591.81
15
Sheet

 

[Holsum]

Thanks so much!