Returning Text in adjacent column

J

Jim411

New Member
Joined
Jan 26, 2012
Messages
7
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
 
Sort by date Sort by votes
Jim411 said:
---------------------------
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
Click to expand...

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
 
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Jim411 said:
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?
Click to expand...
Jim411 said:
I would also like it to skip duplicates if that's possible,
Click to expand...
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?
 
Upvote 0
Peter_SSs said:
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?
Click to expand...

That download didn't seem to do anything, you should be able to copy/paste the below into Excel, thanks
[TABLE="width: 749"]
<tbody>[TR]
[TD]I[/TD]
[TD][/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD][/TD]
[TD]Yes/No[/TD]
[TD]Channel[/TD]
[TD][/TD]
[TD]US-would like to see[/TD]
[TD]US-now shows[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC[/TD]
[TD]ABC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]ABC[/TD]
[TD][/TD]
[TD]NBC[/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD]various[/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD]Customized[/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD]ABC (all year)[/TD]
[TD]various[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD]CBS[/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]various[/TD]
[TD][/TD]
[TD][/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD][/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD][/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD][/TD]
[TD]Customised[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD][/TD]
[TD]NBC[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABC (all year)[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]Customised[/TD]
[TD][/TD]
[TD][/TD]
[TD]CBS[/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]NBC[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]ABC (all year)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]US[/TD]
[TD][/TD]
[TD]Yes, please specify the channel [/TD]
[TD]CBS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]
 
Upvote 0
Jim411 said:
That download didn't seem to do anything,
Click to expand...
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
IJKLMN
1LocationYes/NoChannel
2USNo0
3USYes, please specify the channelABC1
4USNo1
5USYes, please specify the channelNBC2
6USYes, please specify the channelNBC2
7USYes, please specify the channelNBC2
8USYes, please specify the channelvarious3
9USYes, please specify the channelNBC3
10USYes, please specify the channelNBC3
11USYes, please specify the channelNBC3
12USYes, please specify the channelNBC3
13USNo3
14USNo3
15USYes, please specify the channelCustomised4
16USYes, please specify the channelNBC4
17USYes, please specify the channel4
18USYes, please specify the channelABC (all year)5
19USYes, please specify the channelCBS6
206
216
Sheet1




A2 copied down as far as you might ever need.

Excel Workbook
A
1List
2ABC
3NBC
4various
5Customised
6ABC (all year)
7CBS
8
9
Sheet2
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
unique comma separated values base on a text match
Replies
3
Views
94
Fluff
Fluff
L
  • Solved
Multiple Search and Return Formula
Replies
5
Views
147
Peter_SSs
Peter_SSs
C
  • Solved
List Results from All Sheets Search
2
Replies
16
Views
355
MikeVol
MikeVol
J
  • Solved
Return multiple deliminated values from lookup
Replies
2
Views
392
jimbomcmucka
J
T
  • Question Question
Formula to extract information from a table that matches a date
Replies
2
Views
93
tbrynard01
T

Forum statistics

Threads
1,225,479
Messages
6,185,229
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top