Lookup Formula to Replace Incorrect Value

[ericmoran92]

Hello,

I have 2 columns of data:

State	City
Texas	Dallas
Texas	(not set)
Colorado	Boulder
Utah	Salt Lake City

I am trying to use a function in a new column to replace city records which equal "(not set)" and reference the respective cell's state to return the correct value. For the the above table example, the "(not set)" record should be returned as "Dallas".

I have tried to use an index-match function with an approximate match, but it does not return the correct value.

Any assistance will be greatly appreciated!

 

[Fluff]

Hi & welcome to MrExcel.
How about

++Fluff.xlsm
	A	B	C
1	State	City
2	Texas	Dallas	Dallas
3	Texas	(not set)	Dallas
4	Colorado	(not set)	Boulder
5	Colorado	Boulder	Boulder
6	Utah	Salt Lake City	Salt Lake City
Sheet2

Cell Formulas
Range	Formula
C2:C6	C2	=INDEX(FILTER($B$2:$B$6,($A$2:$A$6=A2)*($B$2:$B$6<>"(not set)")),1)