index match

H

Holsum

New Member
Joined
Apr 27, 2018
Messages
5
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

[TABLE="width: 387"]
<tbody>[TR]
[TD]A[/TD]
[TD]B
[/TD]
[TD]C[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]ASSETS
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]Current Assets
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Checking/Savings
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00C · WASHINGTON FEDERAL - C
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00E · WASHINGTON FEDERAL - E
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00H · WASHINGTON FEDERAL - H
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00I · WASHINGTON FEDERAL - I
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-00E · VANGUARD TAX EXEMPT M/M - E
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-00H · CASH - VANGUARD TAX EXMPT M/M
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-00I · VANGUARD TAX EXEMPT M/M - I
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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.


Code: 
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
 
Last edited:
Upvote 0
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
ABCDEFG
1ABCBunsCrustBurgerFindPizza
2DEFSaucePizzaSoup
3GHICheesePastaAppleReturn from
4Column ADEF
Sheet
 
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD]
[TABLE="width: 817"]
<tbody>[TR]
[TD="colspan: 3"]ASSETS
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AF
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 5"]Current Assets
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Checking/Savings
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00C · WASHINGTON FEDERAL - C
[/TD]
[TD="align: right"]56,880.00
[/TD]
[TD][/TD]
[TD="align: right"]51,729.50
[/TD]
[TD][/TD]
[TD="align: right"]5,150.50
[/TD]
[TD][/TD]
[TD="align: right"]-50,545.74
[/TD]
[TD][/TD]
[TD="align: right"]107,425.74
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00E · WASHINGTON FEDERAL - E
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00H · WASHINGTON FEDERAL - H
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]102-00I · WASHINGTON FEDERAL - I
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-00E · VANGUARD TAX EXEMPT M/M - E
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-00H · CASH - VANGUARD TAX EXMPT M/M
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-00I · VANGUARD TAX EXEMPT M/M - I
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-10I · SCHWAB TAXABLE MM - I
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-50H · CASH - FORD TAXABLE MM
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-50I · FORD TAXABLE MM - I
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]103-60H · GOLDMAN SACHS - H
[/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[TD][/TD]
[TD="align: right"]0.00
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]104-00C · STATE BANK OF CHILTON - C
[/TD]
[TD="align: right"]-796.61
[/TD]
[TD][/TD]
[TD="align: right"]10,850.14
[/TD]
[TD][/TD]
[TD="align: right"]-11,646.75
[/TD]
[TD][/TD]
[TD="align: right"]-1,388.42
[/TD]
[TD][/TD]
[TD="align: right"]591.81
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Upvote 0
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?
 
Last edited:
Upvote 0
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
ABCDEFGACADAEAFAGAH
1*Find103-00C**********
2*Return from AF5150.50**********
3**102-00C WASHINGTON FEDERAL - C*****56,880.0051,729.505,150.50-50,545.74107,425.74
4**102-00E WASHINGTON FEDERAL - E*****00000
5**102-00H WASHINGTON FEDERAL - H*****00000
6**102-00I WASHINGTON FEDERAL - I*****00000
7***103-00E VANGUARD TAX EXEMPT M/M - E****004,500.0000
8**103-00H CASH - VANGUARD TAX EXMPT M/M*****00000
9**103-00I VANGUARD TAX EXEMPT M/M - I*****001200
10**103-10I SCHWAB TAXABLE MM - I*****001400
11**103-50H CASH - FORD TAXABLE MM*****00600000
12**103-50I FORD TAXABLE MM - I*****00000
13**103-60H GOLDMAN SACHS - H*****00000
14**104-00C STATE BANK OF CHILTON - C*****-796.6110,850.14-11,646.75-1,388.42591.81
Sheet
 
Upvote 0
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
ABCDEACADAEAFAGAH
1Find102-00H
2Return from AF6580.00
3102-00C WASHINGTON FEDERAL - C56,880.0051,729.505,150.50-50,545.74107,425.74
4102-00E WASHINGTON FEDERAL - E00000
5102-00H WASHINGTON FEDERAL - H006,580.0000
6102-00I WASHINGTON FEDERAL - I00000
7103-00E VANGUARD TAX EXEMPT M/M - E004,500.0000
8103-00H CASH - VANGUARD TAX EXMPT M/M00000
9103-00I VANGUARD TAX EXEMPT M/M - I001200
10103-10I SCHWAB TAXABLE MM - I001400
11103-50H CASH - FORD TAXABLE MM00600000
12103-50I FORD TAXABLE MM - I00000
13103-60H GOLDMAN SACHS - H00000
14104-00C STATE BANK OF CHILTON - C-796.6110,850.14-11,646.75-1,388.42591.81
15
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Index formula with matching - Can it look at rows and columns?
Replies
2
Views
381
usnapoleon
U
U
  • Solved
Index, SumIf?, Matching
Replies
3
Views
178
smozgur
smozgur
C
  • Question Question
Alternative to Index Match to sum up
Replies
4
Views
212
Fluff
Fluff
M
  • Question Question
What am I doing Wrong?
Replies
6
Views
244
MBarrett
M
K
  • Question Question
Index+Match
Replies
4
Views
295
etaf
etaf

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top