Thread: Search for string, report row

I have a somewhat odd need. I have 18 each with 2 sheets (will probably add them into 1 excel file). The sheets are listed by government program and I need to do averages for different programs. The problem is that if NJ doesn't have the program then it gets out. So Education Pensions might be row 45 for NJ but row 47 for NYC. But, each program has a unique identifier. So instead of specifying a sheet, column and row, I want to specify the sheet and column and have a function that repots the row.

Is there a way to do this in Excel 2007?

If you have a common identifier you can use VLOOKUP

=VLookup( value, table_array, index_number, not_exact_match )

Originally Posted by jj72uk

If you have a common identifier you can use VLOOKUP

=VLookup( value, table_array, index_number, not_exact_match )

Hi and thanks. I've tried vlookup but it doesn't seem to work. The two problems with it are:

1) Some of my identifiers are: "1543.AAA". vlookup gives me an error in searching for that.
2) vlookup doesn't report the row, it reports the value. The problem is I don't want the value in the unique identifer, I want the value two columns to the left of that unique identifier. Is there any way I can get that?

If you could post a sheet it would help.

If you have a unique indetifier and you want to get the value from 2 columns left then you'll need to combine INDEX with MATCH

Post a sheet, let me look.

Here is the workbook with just two states. For the example problem I'll give I'm looking at sheets: CTFedSpendList and DEFedSpendList.


WILDLIFE HABITAT INCENTIVE PROGRAM
Identififer (column G): 10.914
Connecticut: Row 86
Delaware: Row 78

So, instead of doing CT per capita spending I want to do the per capita spending of CT and DE (don't worry about populations, I can easily make a table for them to draw from) but I want to automat adding the numbers from these programs together across these 2 worksheets even though they are on different rows. (Ultimately, I'm doing 18 states.)

Any help on that would be amazing!


Secondary question: When I moved the CT sheets into the DE file it now looks for USMaster1 on my C:\ Drive... even though the USMaster1 file is the same in both workbooks... is there a way to make it reference sheet in the new workbook?

Thank you

So, instead of doing CT per capita spending I want to do the per capita spending of CT and DE

You want to add them?

Originally Posted by jj72uk

You want to add them?

Yeah, all I need is to be able to add the money the two states got for any given program in a generalizable form which will allow me to do this for my 18 states over 1,000+ programs. Which means I can't manually input the row number each time.

Originally Posted by jj72uk

Something like this?

=INDEX(CTFedSpendList!D:D,MATCH(G86,CTFedSpendList!G:G,0))+INDEX(DEFedSpendList!D:D,MATCH(G86,DEFedS pendList!G:G,0))

Ah, interesting. I am getting errors and in trying to narrow it down I just used match (which seems promising).

Matching a value on the same sheet I did "=MATCH(96.006,DEFedSpendListG:G,0)" and it reported row 33 which is correct!

But now I want to add the value in B33 ($323247103)with some other number. How do I do that?

"=SUM(B=MATCH(96.006,DEFedSpendListG:G,0),5)" doesn't seem to work. So I can't combine the column letter that I know and the row number that I get from =Match and put that into a =Sum?

Thanks for your help! It seemed really promising but I can't seem to figure out that one piece.

The reason why it doesnt work if you copy and paste directly is the forum has added a uncanny space.

Try copying this

=INDEX(CTFedSpendList!D:D,MATCH(G86,CTFedSpendList!G:G,0))+INDEX(DEFedSpendList!D:D,MATCH(G86,DEFedSpendList!G:G,0))

A nested INDEX MATCH has no problem working together!

I saw before you removed it that you have removed the cell reference with an actual figure, wether numerical or alpha-numerical, this, I think is the problem.

If you just use a cell reference does it work?

Numeric cells such as 10.914 will work fine, if you are using alpha-numeric you will need to add "" to it

eg

"17.FEC"