#### rmrosa

##### New Member
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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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

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

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?

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

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

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

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

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;"")

Replies
1
Views
201
Replies
1
Views
165
Replies
0
Views
124
Replies
2
Views
57
Replies
8
Views
148

1,202,993
Messages
6,052,970
Members
444,623
Latest member
elbertzeeroone

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back