If a cell equals _, at the next row that equals _, return value fr

[Guest]

This is an example of what I'm trying to do:

If a row in Column 1 = Joe's Hardware
Take the next row below Joe's Hardware that is called Paint Brushes and
return the value from Column 7 in the same row

I can't for the life of me figure out how to do it.

 

[Guest]

try
=offset(A1,match("Joe's Hardware",A:A",0),6)

 

[PCLIVE]

Maybe one way could be done like this:

=IF(A18="Joe's Hardware",INDIRECT("G"& (ROW(A18)-1) + MATCH("Paint
Brushes",A18:A100)))

Regards,
Paul

 

[Guest]

This is close - it works if I know where Joe's Hardware is located i.e.
CellA72 - but this will change each month.
I'm working from an exported report that looks something like:



Budgeted Budgeted Actual Actual
Revenue Costs Revenue Costs

Joe's Hardware
Paint Brushes 500 250 350 225
Paint 1000 450 1050 500
Hand Tools 250 125 250 125
Power Tools 5000 2500 5000 3000
Sundries 250 130 250 150
Joe's Hardware(CAD) 7000 3455 6900 4000

Tools 'n Stuff
Paint Brushes 750 400 350 225
Paint 1000 450 1050 500
Hand Tools 250 125 250 125
Power Tools 5000 2500 5000 3000
Sundries 250 130 250 150
Tools 'n Stuff(CAD) 7250 3605 6900 4000

Handyman World
Paint Brushes 500 250 350 225
Paint 750 450 1050 500
Hand Tools 250 125 250 125
Power Tools 5000 2500 5000 3000
Sundries 250 130 250 150
Handyman World(CAD) 6750 3455 6900 4000

Each time we export the data may be in different rows from the previous month.

We are trying to construct a seperate profitability worksheet for each
customer pulling the numbers from the exported data.

So it has to be something like:
When A:A = Handyman World, at the NEXT instance of Sundries return the value
from Column G of that row.

Greatly appreciate the help!

Cathy

 

[PCLIVE]

Ok.

It sounds like you want to first find "Joe's Hardware". Then you want to
find "Paint Brushes" below that.

There are probaly other ways...but see if this works for you.

=INDIRECT("G"&MATCH("Paint Brushes",INDIRECT("A"&MATCH("Joe's
Hardware",A1:A100,0)&":A23"),0)+MATCH("Joe's Hardware",A1:A100,0)-1)

Paul

 

[Guest]

This might just work - but how do I say "return the value 13 rows down from
the MATCH"?

 

[PCLIVE]

This seems a bit more clean using offset recommended by bj.

=OFFSET(INDIRECT("A" & MATCH("Joe's Hardware",A1:A100,0)+1),0,6)

 

[PCLIVE]

Ok, that last one was completely wrong because it assumes that Paint Brushes
is one row below Joe's Hardware.

Try this one:

=OFFSET(INDIRECT("A" & MATCH("Joe's Hardware",A1:A100,0)),MATCH("Paint
Brushes",INDIRECT("A" & MATCH("Joe's Hardware",A1:A100,0) & ":A100"),0)-1,6)

Be care for wrap-around.

HTH,
Paul

 

[Guest]

=offset(A1,match("Joe's Hardware",A:A",0)+13,6)

This might just work - but how do I say "return the value 13 rows down from
the MATCH"?

 

[Guest]

Reading PCLIVE's enteries I think I missunderstood what you were looking for

=offset(indirect("A"&match("Joe's Hardware",A:A",0)),Match("Paint
Brushes",indirect("A"&match("Joe's Hardware",A:A",0)&"A$64000"))-1,7)

 

[Guest]

OK! If every customer's data prints out with the same number of rows i.e. we
have the same # of categories for each I can use:

=OFFSET(INDIRECT("data!A" & MATCH("Joe's Hardware",Data!$A$1:$A$100,0)),13,6)

The simpler of the two.

Otherwise I can use the one that matches both the customer name and the
category.

Thank you both so much! You're geniuses!