Help on matching up names and pulling the spend figure

[Jenko2018]

Hi first post so thank you in advance - after searching around on the internet for solutions I then have came across this forum , so I am really hoping someone can help me out as I do not know where to start on this one as i am very basic in Excel.

I have 3 tabs , tab 1 has data in of the name of the council (column a) and a value (column b) that the rep has entered data into the rows , tab 2 is the second rep with same layout. Tab 3 is my master list and has the full list of councils , but the names in these are spelt slightly different to what the reps have spelt them so for instance Rep 2 has City and County of Swansea , but my master list is spelt Swansea City and Borough Council.



master list layout (tab 3) this is the one I want to do an auto look up on Tab 1 and Tab 2


Column A Column B Column C

[TABLE="width: 915"]
<tbody>[TR]
[TD]Name of council[/TD]
[TD]Rep 1 spend[/TD]
[TD]Rep 2 spend[/TD]
[/TR]
[TR]
[TD]Swansea City and Borough Council[/TD]
[TD]on Rep 1 tab they have spelt this account as " Swansea" in this column on this sheet I want to pull the value through which is on column 2 of Rep 1 tab[/TD]
[TD]in Rep 2 tab they have spelt this account as "City and County of Swansea" in this column I want to pull the value through which is on column 2 of Rep 2 tab[/TD]
[/TR]
</tbody>[/TABLE]

i do not know how to upload the sheet so hopefully this explains

Any help on this I really would be very grateful

Regards

M

 

[mrshl9898]

The only way I see this working is by looking for Keywords you determine.

For example, "Swansea" in A1 on a new sheet then in A2 - =SUMIF(Sheet2!A:A,"*"&A1&"*",Sheet2!B:B)+SUMIF(Sheet1!A:A,"*"&A1&"*",Sheet1!B:B)

The * Before and after the word are wild cards, so will find any cell containing Swansea somewhere.