Returning Text in adjacent column

[Jim411]

In a separate worksheet, using a formula, I want to find certain text in column A (in Sheet1) plus other text in column B (same row) and get the text from column C in the same row. Example: looking for, lets say, "USA" in column A and "Other Lakes" in column B and return the name of a lake from column C, "Lake Sinclair". USA will appear many times and different lakes will appear, the idea is to get a list of the different lakes in a column in a sheet other than Sheet1. The eventual plan is to create a Combo box from this list. I want it in a separate sheet because I want to be able to update Sheet1. There would probably be no more than 200 rows.

Thanks,
Jim

 

[Jim411]

---------------------------
Thanks, thats working, turns out there was a blank space after the word Index, sorry about that (I guess I was copying and pasting the formula too many times). One more thing if possible, there are some cases where the participant didn't fill in anything and it's leaving blank spaces, can that be fixed?

Either way, thanks for all your help.

Jim

I would also like it to skip duplicates if that's possible, now that I've applied it to the most recent worksheet I'm using.

Thanks

 

[Peter_SSs]

One more thing if possible, there are some cases where the participant didn't fill in anything and it's leaving blank spaces, can that be fixed?

I would also like it to skip duplicates if that's possible,

To clarify just where these things are and what you mean, could we see a small set of sample data and expected results that demonstrates the various things you are describing?

 

[Jim411]

To clarify just where these things are and what you mean, could we see a small set of sample data and expected results that demonstrates the various things you are describing?

That download didn't seem to do anything, you should be able to copy/paste the below into Excel, thanks

I		K	L
Location		Yes/No	Channel		US-would like to see	US-now shows
US		No			ABC	ABC
US		Yes, please specify the channel	ABC		NBC	NBC
US		No			various	NBC
US		Yes, please specify the channel	NBC		Customized	NBC
US		Yes, please specify the channel	NBC		ABC (all year)	various
US		Yes, please specify the channel	NBC		CBS	NBC
US		Yes, please specify the channel	various			NBC
US		Yes, please specify the channel	NBC			NBC
US		Yes, please specify the channel	NBC			NBC
US		Yes, please specify the channel	NBC			Customised
US		Yes, please specify the channel	NBC			NBC
US		No
US		No				ABC (all year)
US		Yes, please specify the channel	Customised			CBS
US		Yes, please specify the channel	NBC
US		Yes, please specify the channel
US		Yes, please specify the channel	ABC (all year)
US		Yes, please specify the channel	CBS

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[Peter_SSs]

That download didn't seem to do anything,

Are you referring to Excel jeanie? If so you need to activate it as it is an Add-In. from the link in my signature, click 'How To' at the left. You will then see two 'Activating' links on the left. Assuming you are using a newish Excel version follow the 2007 link. The newer versions all have slightly different procedures or menu options but you should be able to figure it out from that one. Then if you need a guide to its use follow the 'How To' above the 'Activating' links.


Now to the question. In order to keep the formulas from becoming too large/complex I would suggest using a helper column. It could go in either sheet & it could be hidden if you want. I've used column N in Sheet1.

N1 is empty or houses a zero.
N2 is copied down to the end of the data, or beyond if the data might get extended.

Excel Workbook
	I	J	K	L	M	N
1	Location		Yes/No	Channel
2	US		No			0
3	US		Yes, please specify the channel	ABC		1
4	US		No			1
5	US		Yes, please specify the channel	NBC		2
6	US		Yes, please specify the channel	NBC		2
7	US		Yes, please specify the channel	NBC		2
8	US		Yes, please specify the channel	various		3
9	US		Yes, please specify the channel	NBC		3
10	US		Yes, please specify the channel	NBC		3
11	US		Yes, please specify the channel	NBC		3
12	US		Yes, please specify the channel	NBC		3
13	US		No			3
14	US		No			3
15	US		Yes, please specify the channel	Customised		4
16	US		Yes, please specify the channel	NBC		4
17	US		Yes, please specify the channel			4
18	US		Yes, please specify the channel	ABC (all year)		5
19	US		Yes, please specify the channel	CBS		6
20						6
21						6
Sheet1

A2 copied down as far as you might ever need.

Excel Workbook
	A
1	List
2	ABC
3	NBC
4	various
5	Customised
6	ABC (all year)
7	CBS
8
9
Sheet2

 

[Jim411]

Thanks for all your help!

Jim