# Thread: Search thru file and calc formulas (Excel xp)

1. ## Search thru file and calc formulas (Excel xp)

I have the spreadsheet attached. I need to search thru a file that has 50 of the tables attached (the attached only has 2). The cells that are red are the ones i need to find for all 50 tables. So in the first table, i need to look thru B8 to B17 and find the first cell that is <20. Then I need to put the value of that cell in H5 and the dollar amt it corresponds to in H4. I need to do cars 1 thru car 6 and then move on to the next table. Each "table" is separated by #page so i can search for page.

Could i get some help with the code...

search for "#page"
car1 code to find first value <20, get corresponding dollar amount in COL A
car1 code to find first value >70, get corresponding dollar amount in COL A
go to car2
...

Thank you for the help.

2. ## Re: Search thru file and calc formulas (Excel xp)

Instead of a macro, the following formulas, give what you want.
In H4 (with "<") you could enter something like:
<pre>=INDEX(\$A8:\$A17,MATCH(20,B8:B17,-1)+1)
</pre>

In H5:
<pre>=INDEX(B8:B17,MATCH(20,B8:B17,-1)+1)
</pre>

For the ">" [This will actually find ">="] in I4/I5
<pre>=INDEX(\$A8:\$A17,MATCH(70,B8:B17,-1))
=INDEX(B8:B17,MATCH(70,B8:B17,-1))
</pre>

You could put the 20 and 70 in a cell and reference them if desired, and these could be copied at the top of each of the tables.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•