()Index Match if conditions met

[Guest]

Hello helpful ones,
This formula is not producing desired results and unsure what to do
=IF(INDEX($T$3:$T$21,MATCH(E3,$R$3:$R$21,0))="NA",INDEX($X$3:$X$21,MATCH(E3,$R$3:$R$21,0)),INDEX(X3:X21,MATCH(B3,IF(SUMPRODUCT(T3:T21<>B3)*(U3:U21<>B3)*(V3:V21<>B3)*(W3:W21<>B3)),$X$3:$X$21,0),0))
..
if condition one is met, range T3:T21 (if target cell = "NA")
true result produces value of target cell in range x3:x21 (a $ amount).
THIS PORTION WORKS FINE, PROBLEM IS WHEN
..
if condition one is FALSE, then formula should only produce target cell
value in range (x3:x21) if range T3:X3 <> cell value in B3.

If this makes sense, GREAT, if not please reply.
Thanks,
Steven - Dallas

 

[Frank Kabel]

Hi
not really sure, but try the array formula (entered with
CTRL+SHIFT+ENTER):
=IF(INDEX($T$3:$T$21,MATCH(E3,$R$3:$R$21,0))="NA",INDEX($X$3:$X$21,MATC
H(E3,$R$3:$R$21,0)),INDEX(X3:X21,MATCH(1,($X$3:$X$21=B3)*(T3:T21<>B3)*(
U3:U21<>B3)*(V3:V21<>B3)*(W3:W21<>B3),0))

--
Regards
Frank Kabel
Frankfurt, Germany

Hello helpful ones,
This formula is not producing desired results and unsure what to do:

=IF(INDEX($T$3:$T$21,MATCH(E3,$R$3:$R$21,0))="NA",INDEX($X$3:$X$21,MATC

 

[Guest]

Thanks Frank, but still didn't work.

Can you tell what 1 represents in ...MATCH(1,($X$3:$X$21=B3)....
essentially rather than in 1st statement, $T$3:$T$21 ="NA" needs to be
change to
$T$3:$X$21 <> B3.

does that help.
have always found your posts very helpful by the way!

Brgds,
StevenT

 

[Dave R.]

It looks like the issue is that, in the second INDEX/MATCH statement, you
are embedding an if statement that you want to return a range (as text) and
work with the formula. It might work (haven't tried) if you used an INDIRECT
statement around the "X3:X21" range.

But a simpler approach, if I'm understanding, would be to set the IF
statement up BEFORE the second index/match.. Something like

<first, working index/match>, IF(sumproduct(<stuff not = to
b3>),INDEX(X3:X21, MATCH(B3,X3:X21,0),"")

Hope that makes sense, and that it will work for you. This returns a "" if
the second IF statement fires and the sumproduct statement is equal to 0.

Hello helpful ones,
This formula is not producing desired results and unsure what to do:

=IF(INDEX($T$3:$T$21,MATCH(E3,$R$3:$R$21,0))="NA",INDEX($X$3:$X$21,MATCH(E3,

 

[Frank Kabel]

Hi
have you entered the formula as array formula?
The '1' represents the result from this multiplication

 

[Guest]

Dave, let me start over.
A B C D E F
1 AAA TT TT AAA 0.10
2 BBB UU TT DDD 0.15
3 CCC XX UU 0.20
4 DDD TT XX 0.20

formula contained in - C1
IF B1 = D14 , F1:F4. except when E1:E4 = A1

as you can see B1 = both D1 & D2, but E1 = A1
therefore correct entry for C1 would be E2.

I in using your solutions came up with a similar response to my own formula.
Problem seems to be when formula comes to first correct column D value, if
the other conditions are not met, it cannot go on to the next column D value
which is correct.

Hope this simplifies.
Thanks for your time
StevenT

 

[Frank Kabel]

Hi Steve
you may post your eXACT formulas currently uised

 

[Guest]

Frank, this is it
=IF(INDEX($T$3:$T$21,MATCH(E3,$R$3:$R$21,0))="NA",INDEX($X$3:$X$21,MATCH(E3,$R$3:$R$21,0)),INDEX(X3:X21,MATCH(B3,IF(SUMPRODUCT(T3:T21<>B3)*(U3:U21<>B3)*(V3:V21<>B3)*(W3:W21<>B3)),$X$3:$X$21,0),0))

What trying to do is fill in cell with value in range x3:x21 if
e3 = r3 and t3:w21 <> b3.

Thx

 

[Frank Kabel]

Hi
I suggested one formula previously. Have you tried it with the
additions/explanations I made?.
You may also email me an example file (and state your expected result
in this example)

--
Regards
Frank Kabel
Frankfurt, Germany

Frank, this is it:

=IF(INDEX($T$3:$T$21,MATCH(E3,$R$3:$R$21,0))="NA",INDEX($X$3:$X$21,MATC