Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula that says: If column "C" is equal to any value in range A1:A21, then it is "1".
Solved by S. Y. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
29/08/2018 - 01:37
Hi
User
29/08/2018 - 01:37
hello
Excelchat Expert
29/08/2018 - 01:38
can you explain with an example..
Excelchat Expert
29/08/2018 - 01:38
like with sample data
User
29/08/2018 - 01:39
yes. I have a range of zip codes and a list of potential client sites that are arranged by zip code and I'm trying to write a formula that says "if within this zip code range, then "district 1"
User
29/08/2018 - 01:39
I'll paste
Excelchat Expert
29/08/2018 - 01:39
sure
Excelchat Expert
29/08/2018 - 01:40
so if we find 90001 in column A you need "1" right
User
29/08/2018 - 01:40
So im trying to say if any of the zip codes on the left match the ones in the blue list, then district 1
Excelchat Expert
29/08/2018 - 01:41
so a1 should be in column C
Excelchat Expert
29/08/2018 - 01:41
right
User
29/08/2018 - 01:41
sure
User
29/08/2018 - 01:41
im lost rn so I'll take it that you're right
Excelchat Expert
29/08/2018 - 01:44
can you check column E
User
29/08/2018 - 01:44
HA!
User
29/08/2018 - 01:44
YES
Excelchat Expert
29/08/2018 - 01:45
:)
User
29/08/2018 - 01:45
now here is the secondary issue
Excelchat Expert
29/08/2018 - 01:45
ok
User
29/08/2018 - 01:45
I have 5 districts
User
29/08/2018 - 01:45
that need similar format styling
Excelchat Expert
29/08/2018 - 01:46
ok so you want to search A1 in 5 column to check which district it belongs to right
Excelchat Expert
29/08/2018 - 01:46
?
User
29/08/2018 - 01:46
I'm being redundant... I apologize. I should just share the dang spreadsheet
Excelchat Expert
29/08/2018 - 01:47
yes that would be great
User
29/08/2018 - 01:47
[Uploaded an Excel file]
User
29/08/2018 - 01:48
There is a single column for district and I need to run a similar formatting style to what you just did for it to differentiate between the 5 districts
Excelchat Expert
29/08/2018 - 01:49
in sheet 2 i see district 1
Excelchat Expert
29/08/2018 - 01:49
where are the zipcodes for other districts?
Excelchat Expert
29/08/2018 - 01:50
ok in Sheet1 you have 1-5 marking of the district?
Excelchat Expert
29/08/2018 - 01:50
so for example 90002
belongs to 2nd district right?
User
29/08/2018 - 01:51
Exactly
User
29/08/2018 - 01:51
the x's mark what district
Excelchat Expert
29/08/2018 - 01:51
ok in case there are 2 "x" then only 1 will come up ok
User
29/08/2018 - 01:51
okay
User
29/08/2018 - 01:51
makes sense and i saw that but wasn't sure how it would label the,
User
29/08/2018 - 01:51
them**
Excelchat Expert
29/08/2018 - 01:52
give me few min to build the formula
User
29/08/2018 - 01:52
thank you very very much
Excelchat Expert
29/08/2018 - 01:58
[Uploaded an Excel file]
Excelchat Expert
29/08/2018 - 01:59
you there
User
29/08/2018 - 01:59
yes
Excelchat Expert
29/08/2018 - 02:00
does the solution look good
User
29/08/2018 - 02:00
So most of them filled with district 1 2 or 3 but then there is a stent of "No Match". What's that about?
User
29/08/2018 - 02:00
it looks great otherwise btw
User
29/08/2018 - 02:00
lifesaver
Excelchat Expert
29/08/2018 - 02:01
no match means that the zipcode wasnt fond in the sheet
Excelchat Expert
29/08/2018 - 02:01
91764 for example sint there is sheet 1
Excelchat Expert
29/08/2018 - 02:01
91765
91766
91767
Excelchat Expert
29/08/2018 - 02:01
91759
91765
91766
91767
Excelchat Expert
29/08/2018 - 02:02
60-64 series is missing
Excelchat Expert
29/08/2018 - 02:02
and so on
User
29/08/2018 - 02:02
or right. The zips were specific to :A
User
29/08/2018 - 02:02
LA
User
29/08/2018 - 02:02
and those dont fall in the range of LA
User
29/08/2018 - 02:03
I get it.
Excelchat Expert
29/08/2018 - 02:03
try changing one for the zip codes in no match with something that is there in the sheet 1 then you will see formula will work for all the districts
Excelchat Expert
29/08/2018 - 02:03
:)
Excelchat Expert
29/08/2018 - 02:03
5 dosent have codes
Excelchat Expert
29/08/2018 - 02:03
sorry i mean both 4-5 have few codes
User
29/08/2018 - 02:04
and they overlap with 1 or 2 and those are first checks so it defaults to 1 or 2 instead of 5?
Excelchat Expert
29/08/2018 - 02:04
but the problem is whichever are 5 or 4 are also 1 so as mentioned only 1 district will come that that would be the 1st district
User
29/08/2018 - 02:04
right
Excelchat Expert
29/08/2018 - 02:04
yes ur right
User
29/08/2018 - 02:05
Well you were awesome.
Excelchat Expert
29/08/2018 - 02:05
:)
Excelchat Expert
29/08/2018 - 02:05
Thanks
Excelchat Expert
29/08/2018 - 02:05
Please do click on end session and leave a feedback
User
29/08/2018 - 02:05
thanks again!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
