Return value problem please help!!!

[rmrosa]

Hi to all,

This is my first time at the forum, so I will try to explain the best I can my doubt.

I have a problem with a return value.

Imagine that I have several values in a range that is a5:d20, and in the cell e5 I have a value, and another one in the cell f5.

Now I want to check if the values that are in the cell’s e5 and f5 exist in the range a5:d20, if they exist I want to return the value that is in the cell d5.

I don’t now if any of you guys will understand my doubt, but I try to post a picture but didn’t work I don’t now why.

So if you guys can help me I really appreciate.

Tanks

Ricardo

 

[Dutchy]

The following formula will return the value from cell D5 if the values in BOTH E5 and F5 appear (once or more) in the range A5:D20:

=IF(AND(COUNTIF(A5:D20,E5)>0,COUNTIF(A1:D20,F5)>0),D5,"")

Is that what you need?




edited

 

[rmrosa]

Hi Dutchy,

in fact that formula return the value that is in the cell d5, but if you pull down the cursor the formula will return all values that is in the column d,

and want I want is only return the value in the column d if the value in column e and f are = to the one that are in the column a and b.

I don't now if I explain better now, I hope sow

 

[Dutchy]

If you only want D5 when E5=A5 and F5=B5 then:

=IF(AND(E5=A5,F5=B5),D5,"")

or do you want D5 if either one of these is true, in which case:

=IF(OR(E5=A5,F5=B5),D5,"")

or is it something different?

 

[rmrosa]

in fact I only want D5 when E5=A5 and F5=B5, but the problem is that I have several values in the range a5:d20 that are equal to the values in e5 and f5, so I have to fix the cell e$5 and f$5 to pull down the cursor and make the check in all range.

If can give me one adress of an e-mail I can send you an example of that.

this is a problem that a realy can't solve, so if can help me I will stay very hapy.

best regards,

Ismael

 

[Dutchy]

OK,

now you've lost me a little, Ismael!

Where do you want the result(s)?

Is there one result or several?

Does =IF(AND(E5=A5,F5=B5),D5,"") in G5 give you what you want? Then it seems you want to copy this down column G.

What do you want in row 6 - are you looking to compare A6 with E5 and B6 with
F5 and if both true give D5 again?

I need a bit more detail to be able to help you more, thanks

 

[rmrosa]

Hi Dutchy,

I am going to try to explain better now.

So this doesn’t give me war I want =IF(AND(E5=A5,F5=B5),D5,"") in G5,

I want compare A5 with E5 and B5 with F5 and if both true give D5, this in G5, then in G6 I want to compare A6 with E5 and B6 with F5 and if both true give D6, then make the same sting with A7 and B7, in order to copy this down column G.

I hope this help

Thanks

 

[Dutchy]

OK,

Then I think we were nearly there.

You just want:

=IF(AND(E$5=A5,F$5=B5),D5,"")

in G5 copied down column G

 

[rmrosa]

Hi Dutchy,

I already made same changes in the formula and now works fine,

a type the formula like this

IF(AND($E$5=$A5;$F$5=$B5);D5;"")

thanks for your precious help