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

#### Rick187

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

##### Well-known Member
Code:
``=IF(A1=B1,C1,"")``
This is the simplest of formulas... Maybe consider googling the problem before opening a new thread?

#### Rick187

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

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

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

#### Rick187

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

Code:
``[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_

##### Well-known Member
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

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

seen post#7

Last edited:

#### Tim_Excel_

##### Well-known Member
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

##### Well-known Member
Hi,

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

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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Cat</td><td style=";">London</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dog</td><td style="text-align: right;;"></td><td style=";">New York</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Pig</td><td style=";">No Match</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Mouse</td><td style="text-align: right;;"></td><td style=";">Paris</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Chicken</td><td style=";">No Match</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Cat</td><td style="text-align: right;;"></td><td style=";">London</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Dog</td><td style=";">New York</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Horse</td><td style="text-align: right;;"></td><td style=";">California</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Cow</td><td style="text-align: right;;"></td><td style=";">Washington</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet558</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">2,1/SEARCH(<font color="Green">A2,G\$2:G\$6</font>),I\$2:I\$6</font>),"No Match"</font>)</td></tr></tbody></table></td></tr></table><br />

Threads
1,084,776
Messages
5,379,812
Members
401,629
Latest member
LEMANOIS