If one cell contains X make other cell contain Y

[fuzzyfuzzy]

Hi,

Been struggling with this for a few days and given up. Hopefully you guys will help.

Basically, I want a column to recognise if another cell in the same row contains x then it will display Y

For instance, if B contains one of the cities, I want E to auto-populate the number for each


A B C D E
1 London 10
2 Manchester 12
3 Birmingham 6
4 Newcastle 11
5 Leeds 2
6 Sheffield 9
7 Norwich 6
8 Cardiff 5


I've tried all sorts and going round in circles, i'm sure it's fairly straight forward for those in the know but i'm totally lost with it now. Any help, very much appreciated.

Cheers

 

[fuzzyfuzzy]

Formatting of this thread has gone wonky since it was posted!

ABCDE were spaced out so that the city fell under column B and number under column E

 

[fuzzyfuzzy]

Forgot to mention.

The city is just part of the contents of the cell. That's my issue. If it just the city in the cell it's fine but I need the city to be recognised in just part of the address.

A
1
2
3
4
5
6
7
8

 

[Eric W]

Your question is a bit hard to understand, partly due to the formatting issues you're having. To format a section of a sheet, you can use one of the special tools - see the HTML Maker in my signature. It will generate something like this:

	B	C	D	E	F	G	H	I
Address	City	Number
Downtown Leeds	London
London Bridge	Manchester
123 Main Street, Newcastle	Birmingham
Cardiff Fork	Newcastle
221B Baker Street, London	Leeds
Sheffield
Norwich
Cardiff

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]12[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]11[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=LOOKUP(2,1/FIND($H$2:$H$9,B2),$I$2:$I$9)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The best guess I have for your question is if you have an address in column B, you want to pull a matching number from another table for the city. The formula above should do that. If this isn't it, let me know.

 

[fuzzyfuzzy]

Thanks very much for your help Eric.

Your suggestion works, but, it doesn't recognise where the city forms part of the address in the one cell.

So if the cell contains "123 John St, Leeds" it wont find it. If the cell only contains "Leeds" it works.

Is there a way around this?

Thanks in advance!

Cheers

 

[Aladin Akyurek]

Try...

=LOOKUP(9.99999999999999E+307,SEARCH(Cities,$A2),Numbers)

where Cities is a range housing cities of interest and Numbers the corresponding numbers next to Cities.

A2 is a string like 123 John St, Leeds.

 

[fuzzyfuzzy]

Thanks Aladin,

I cant get that to work

This is how i have interpreted the formula on my particular sheet

=LOOKUP(9.99999999999999E+307,SEARCH(Cities,$H24:H34),Numbers,$J24:J34)


I get an error of "too many arguments for this function". The cities i want it to find are in H24:H34 and the numbers I want it to show if it finds a match are in J24:J34

 

[Aladin Akyurek]

Thanks Aladin,

I cant get that to work

This is how i have interpreted the formula on my particular sheet

=LOOKUP(9.99999999999999E+307,SEARCH(Cities,$H24:H34),Numbers,$J24:J34)


I get an error of "too many arguments for this function". The cities i want it to find are in H24:H34 and the numbers I want it to show if it finds a match are in J24:J34

H24:H34 contains a list of cities and J24:J34 the corresponding numbers, right? If so:

=LOOKUP(9.99999999999999E+307,SEARCH($H$24:$H$34,$A2),$J$24:$J$34)

where A2 houses a target string like "123 John St, Leeds".

 

[fuzzyfuzzy]

BOOM!

Thanks so much Aladin, sorted.

Really appreciate your help.

Cheers!!

 

[fuzzyfuzzy]

Sorry, one last question.

Can you alter the formula so that any currently un-populated cell with that formula doesn't show "n/a" but is just blank?

Thanks