Using offset within a formula dynamically

[S Davis]

Hello.

What I am trying to do is return a value that is in column G *IF*
column A and column B meet my criteria. Column G is a quantity value,
and column A is a Vehicle Type and column B is Job Type.

So, if colA = Car and colB = Tires, then return the quantity of work
needed to be done.

Sounds like a simple problem. The thing is, I need to find the instance
in which the two factors are met (Car and Tires) and then return the
quantity within that row. I never know which row it may be however...
essentially, Id like to offset from the row position that the two
criteria are met.

Possible?

Sorry if this is confusing... but trust me, its more confusing here in
person.

 

[S Davis]

Is there some way i can build in the =ROW function to find the location
of multiple criteria?

 

[Peo Sjoblom]

One way

=INDEX(G2:G100,MATCH(1,(A2:A100="car")*(B2:B100="Tires"),0))

It needs to be entered with ctrl + shift & enter

replace car and Tires with cell references and put the criteria in cells
instead, that way you don't have to edit the formula when you change
criteria
--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com

 

[VBA Noob]

Something like this

Change Enter your sum or formula here !! to calculations

=IF(AND(A2="Car",B2="Tires"),"Enter your sum or formula here !!","")

VBA Noob

 

[Tom Hutchins]

Use SUMPRODUCT, like this:
=SUMPRODUCT((A1:A100="Car")*(B1:B100="Tires")*G1:G100)

See this link to learn all about SUMPRODUCT:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Hope this helps,

Hutch

 

[S Davis]

Thank you so incredibly much Peo!!!!! I literally cheered here in my
office... almost had nothing to show for the day

Truly, thanks!

-Sean

PS - here is the final formula for anyone's future reference - just
change out the named ranges to your own:

=IF(ISERROR((OFFSET(INDEX(STD_Obj_ManufactModel,MATCH(1,(STD_Obj_ManufactModel=$AU9)*(STD_Desc2=$AV9),0)),0,6)*K9)),0,(OFFSET(INDEX(STD_Obj_ManufactModel,MATCH(1,(STD_Obj_ManufactModel=$AU9)*(STD_Desc2=$AV9),0)),0,6)*K9))

 

[S Davis]

Alright. Let's take this a step further.

What the formula below is doing is looking up where in a list two
values are met in the same row, and then offsetting out to return a
third number within that row (and then multiply it by a value).
Basically, its saying 'when there is a certain type of vehicle and a
certain type of repair, display the quantity of parts needed for that
repair'.

The thing is, there are many times within the reference data that a
certain vehicle and repair are displayed. This is because every part
required, which has a quantity value, says the vehicle and repair type
for that part. Obviously most repairs require more than one part.

So what I need to do is find EVERY instance that vehicle and repair
match up, and then return the sum of all parts (qty) required.
Essentially:

Part1...Car 1...Repair1...Qty
Part2...Car 1...Repair1...Qty
Part3...Car 1...Repair1...Qty
Part4...Car 2...Repair1...Qty
Part5...Car 3...Repair2...Qty

What I want to do is return the total QTY for all Car1 with Repair1.
The result will be the total number of parts required, as this figure
will be multiplied by the forecast amount of workload (ie. the number
of times the repair is expected to happen).

Within the formula below, what's inside the offset brackets returns the
Qty - Qty is 6 columns out within the list I have. K9 is the workload
figure.

Thanks