Urgent help please! I need a very specific 'if' formula

[joshlally]

Hi all,

Thanks for taking the time to try and help me out. I basically have an excel sheet with a list of towns on it, and a 'yes/no' column for each of the rows containing towns. What I would like to do is (on a separate sheet, or a separately linked workbook if possible) create a formula which checks to see if an of the towns i have listed have a yes or no by them; and if yes - copy the town name into a column of its own. I have attached a mock up table showing what i described above if anyone could help!

Many thanks for all your time.

Josh

Example:

town	confirmed
london	yes
manchester	yes
birmingham	no

<tbody>
</tbody>

...and because rows 3 and 4 are 'yes' I would like the exact text in those cells to show up on a separate sheet.

 

[joshlally]

Thanks! I tried putting your formula in J2, However when I enter data into any cell from J3 downwards, it doesnt find and duplicate that across to F3 downwards. Any suggestions? I confirmed with crtl + shift + Enter

 

[joshlally]

so just to make that celar what i want to do is input the data next to the cities generated by your formula - then have that ID in J link back to the corresponding city in the F column

 

[t0azt]

Sounds like you just need a vlookup. Try this formula in F3

 IF(E3="y", VLOOKUP(D3, $I$3:$J$9,2,0),"")

 

[platonic567]

	D	E	F	G	H	I	J
1	CITY	POSTCODE	ID			RESULT	ID
2
3	HO CHI MINH	y	100			HO CHI MINH	100
4	HO CHI MINH	n				HA NOI	200
5	HO CHI MINH	n				HA NOI	200
6	HO CHI MINH	n				HA LONG	300
7	HO CHI MINH	n				CAN THO	400
8	HO CHI MINH	n				HAI PHONG	500
9	HA NOI	y	200			DA NANG	600
10	HA NOI	y	200
11	HA LONG	y	300
12	CAN THO	y	400
13	HAI PHONG	y	500
14	DA NANG	y	600
15	BIEN HOA	n
16	VUNG TAU	n
17	QUI NHON	n
18	BINH DUONG	n

<TBODY>
</TBODY>

Formula in I3:

=IFERROR(INDEX($D$2:$D$17,SMALL(IF($E$2:$E$17="y",ROW($D$2:$D$17)-ROW($D$2)+1),ROWS($D$1:D1))),"")

Formula in J3:

=IFERROR(INDEX($F$3:$F$18,SMALL(IF($E$3:$E$18="y",ROW($D$3:$D$18)-ROW($D$3)+1),ROWS($D$1:D1))),"")

Confirm both with CTRL+SHIFT+ENTER