IF value is same in two columns then display a third value from another column

[Rick187]

Hi,

Does anyone know which formula can achieve this:

If value in column A is the same as value in column B, then display the value from Column C

For example if Column A contains these values

C1 - AAA
C2 - BBB

Column B contains the same:
B1 - AAA
B2 - BBB
B3 - CCC

Column C contains
C1 - London
C2 - New York
Etc.

In Column D, I would like it to insert the value from Column C, so next to "AAA", I would like it to display "London"

Any help would be appreciated.

Thanks in advance.

 

[Tim_Excel_]

=IF(A1=B1,C1,"")

This is the simplest of formulas... Maybe consider googling the problem before opening a new thread?

 

[Rick187]

Unfortunately this doesn't seem to work when the columns are in different sheets.

I've tried this: =IF('Sheet1'!A:A=Sheet2!G:G,Sheet2!I:I,"")

 

[Tim_Excel_]

Again, Google is your best friend... (although one could argue it isn't because of privacy reasons, but that's another topic)

[COLOR=#333333] =IF([/COLOR]Sheet1![COLOR=#333333]A:A=Sheet2!G:G,Sheet2!I:I,"")[/COLOR]

 

[Rick187]

Again, Google is your best friend... (although one could argue it isn't because of privacy reasons, but that's another topic)

[COLOR=#333333] =IF([/COLOR]Sheet1![COLOR=#333333]A:A=Sheet2!G:G,Sheet2!I:I,"")[/COLOR]

This doesn't seem to work. Nothing displays when I use this formula.

 

[Tim_Excel_]

It does work on my end when I use regular text and number values. I'm assuming there's some information missing here. What do these ranges contain?

 

[Rick187]

It does work on my end when I use regular text and number values. I'm assuming there's some information missing here. What do these ranges contain?

They contain text.

In Sheet 1 If the value from A2 is contained in a range from another sheet (Sheet 2 Column G), then Display the corresponding Value from (Sheet 2 Column I)

So if I have the word "Cat" in the the first sheet in Cell A2, and in sheet 2 Column G I have 30 different words and one of them is "Cat", Next to the word "Cat" in "Column I" It says "London". I would like the word "London" to display in Sheet 1 cell B2.

 

[Fluff]

seen post#7

 

[Tim_Excel_]

That's a whole 'nother cookie

For this you will have to use the MATCH function. This will return the row number of the found value, or #N/A if the value was not found. You can then return the value of the cell next to it. Use the IFNA function to handle values that won't be found.

 

[jtakw]

Hi,

Change/adjust cell references/range, add sheet name, as needed.

Change "No Match" to "" (Blank) or whatever you like, formula copied down:

Book1
	A	B	C	D	E	F	G	H	I
2	Cat	London					Dog		New York
3	Pig	No Match					Mouse		Paris
4	Chicken	No Match					Cat		London
5	Dog	New York					Horse		California
6							Cow		Washington
Sheet558

Cell Formulas
Range	Formula
B2	=IFERROR(LOOKUP(2,1/SEARCH(A2,G$2:G$6),I$2:I$6),"No Match")