fill an array with two different functions, how?

amyfb

New member
Joined
Dec 18, 2020
Messages
24
Reaction score
0
Points
1
Location
Southeastern PA, USA
Excel Version(s)
O365
I have two perfectly running INDEX MATCH MATCH formulas returning two different sets of results from two different tables.
One of the results is a factor to achieve a calculated result, and the other is a fixed value. I can get the factor and complete the calculation, and, I can pull the fixed value. I cannot do them both together, it's one or the other.

How do I join these two Formulas so that they are one, and my master file populates?

I have tried:
IFS - but it stops after the first TRUE.
& - pretty sure i got all the extra brackets in

I suppose it could be that i'm using an ineffective data table approach, but I can't figure any other way to manage a data table with dynamic values and another with fixed values, all of which need to come together into one file.

Many thanks in advance for ideas.

amyfb
 
Solved with better data tables

I tried IF(AND as well, and failed. so far....

I had to rethink the way my data tables were arranged. Which led to another IF plus an Xlookup to solve the first easy one-way condition, before moving on to the other two 2-way conditions using an INDEX MATCH MATCH setup.

Now I just have to get the data tables filled up to resolve the #Div/0! errors.

Thanks for listening to me think out loud. Sometimes that is all it takes.

I love the new (to me) dynamic functions in Excel365. It's a breath of fresh air from getting errors reduced because of manual adjustments.
CHeerios
Amyfb
 
Back
Top