If column X equals A and column Y equals B then show what's in column Z

[HEzim]

Hi everyone,

Stuck on creating a formula in excel.
Column x,y and z are next to each other. I need a formula that functions like this:
if column x equals "a", and if column y equals "b" then pull/show what's in column z

I tried using If functions but I can't seem to get them to apply to entire columns.

Please help.

 

[Joe4]

It would look something like this (for row 2):

=IF(AND(X2="a",Y2="b"),Z2,"")

 

[HEzim]

It would look something like this (for row 2):

=IF(AND(X2="a",Y2="b"),Z2,"")

The formula does not work.
It pulls blanks when I try to search through entire columns. ie.
=IF(AND(X:X="a",Y:Y="b"),Z:Z," ")

 

[Joe4]

The formula does not work.
It pulls blanks when I try to search through entire columns. ie.
=IF(AND(X:X="a",Y:Y="b"),Z:Z," ")

That is because I thought you were checking one row at a time, and returning a result to each row (it was not very clear from your original post).

If you are searching for "a" anywhere in column X, and "b" anywhere in column Y, then how do you know which row to return the value from column Z for?
Or, are are you really trying to do a lookup on two columns, find which row has "a" in column X AND has "b" in column Y, and return the value from column Z of that particular row?
If that is the case, is it possible that there might be more than one match, and if so, what do you want to happen in that situation?

 

[HEzim]

That is because I thought you were checking one row at a time, and returning a result to each row (it was not very clear from your original post).

If you are searching for "a" anywhere in column X, and "b" anywhere in column Y, then how do you know which row to return the value from column Z for?
Or, are are you really trying to do a lookup on two columns, find which row has "a" in column X AND has "b" in column Y, and return the value from column Z of that particular row?
If that is the case, is it possible that there might be more than one match, and if so, what do you want to happen in that situation?

I was worried I hadn't communicated the issue properly.

"are you really trying to do a lookup on two columns, find which row has "a" in column X AND has "b" in column Y, and return the value from column Z of that particular row?"
This is what I was trying to do.

Given the data I am working with, I am confident that 99.9% of the time there will not be more than one match.
I will put a control in place to ensure it (reconciliation summation).

Do you know the formula that might be able to help me out?

 

[Joe4]

There are a few different ways to do that.
This article shows you a few, and walks you through how to set it up: Using VLOOKUP on multiple columns

 

[Joe4]

I would probably opt for the INDEX/MATCH formula in that example.
If the value you wanted to search column X for is in cell T1 and the the value you want to search column Y for is in cell U1, then the formula would look like this:

{=INDEX(Z:Z,MATCH(1,(X:X=T1)*(Y:Y=U1),0))}

(and note that the { and } are really not part of the formula, but indicates that the formula was entered with CTRL+SHIFT+ENTER instead of just ENTER).

 

[HEzim]

I would probably opt for the INDEX/MATCH formula in that example.
If the value you wanted to search column X for is in cell T1 and the the value you want to search column Y for is in cell U1, then the formula would look like this:

{=INDEX(Z:Z,MATCH(1,(X:X=T1)*(Y:Y=U1),0))}

(and note that the { and } are really not part of the formula, but indicates that the formula was entered with CTRL+SHIFT+ENTER instead of just ENTER).

I must be typing it in incorrectly but it doesn't seem to pull anything. I get #N/A

 

[Joe4]

Please post an example, a small sample of your data, and your formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[HEzim]

Please post an example, a small sample of your data, and your formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Unfortunately, Im on a work computer and I cannot download new add ins.

Do you think you could tell me the other way you had in mind?