How to look up 2 Cell Values

[PR]

I wish to lookup 2 cells and if they = a certain value then set number in
another cell...
eg:

if
b2 = a4 and d2 = 1 then cell x2 = 10
b3 = a4 and d3 = 4 then cell x3 = 8
b4 = a3 and d4 = 4 then cell x4 = 8

and so on...

 

[Rick Rothstein]

For your first relationship, you would put this in X2...

=IF(AND(B2=A4,D2=1),10,"")

Follow the same construction layout for your other relationships.

Note, since you didn't say what you wanted if the test failed, I outputted
the empty string ("")... you can change that as needed.

 

[Bob Bridges]

I guess you want the IF and AND functions, then. The AND function takes two
or more conditions and returns TRUE if they're all TRUE, FALSE otherwise.
Like this:

=AND(B2=A4,D2=1)

That returns either TRUE or FALSE. Now, before you combine it with the IF
function you need to fill in a blank you didn't mention before: What do you
want X2 to be equal to if the above is NOT true?

 

[PR]

I would like also to check the same cell if it is =

if
b2 = a4 and d2 = 1 then cell x2 = 10
OR
b2 = a4 and d3 = 4 then cell x3 = 8
OR
b2 = a3 and d4 = 4 then cell x4 = 8
else 0

 

[Rick Rothstein]

Then (using the 2nd relationship as an example) I don't understand your
specifying the "then cell x3 = 8" part... if that doesn't mean Cell X3, what
does it mean? Just so we are clear about how Excel works, you can't have a
formula in one cell "push" out a value into another cell... cells can only
"pull" values in... so each cell that you want to display a value in has to
have its own formula.

 

[PR]

X3 is the cell that I am putting the forumla in... does this make sense?

Paul

 

[Rick Rothstein]

Then I stand by my first posting. Put this in X2...

=IF(AND(B2=A4,D2=1),10,"")

and using the same formula construction, put this in X3...

=IF(AND(B2=A4,D3=4),8,"")

and continue constructing the formulas you put in your other cells in the
same way.

 

[PR]

many thanks, but how do I find out if in X2... if B2=A4,D3=4 then I would
want X2 to = 8 not 10...

again, does this make sense?

Paul

 

[Rick Rothstein]

Now I have to go back to my confused state and wonder, as I mentioned
previously, about your use of "then cell x3 = 8" in that original 2nd
formula and now also your mentioning "X3 is the cell that I am putting the
formula in" in your previous post. I am really confused as to what tests are
supposed to go into what cells.

 

[PR]

i should start again...

I wish to set a cell to = a value based values in 2 different cells
so I wish the value to be store in cell X2 the two cells I am looking in are
B2 and D3
eg:

IF B2 = 4 and D2 = 1 then cell X2 = 10
OR
IF B2 = 4 and D3 = 4 then cell X2 = 8
OR
IF B2 = 3 and D4 = 4 then cell X2 = 6
OR
IF B2 = 5 and D4 = 1 then cell X2 = 12

Regards - Paul