IF and VLOOKUP

Resolved · Medium Priority · Version 2010

Thu 8 Aug 2013, 16:38 Delegate Mathanki said...

Mathanki has attended:
Excel Advanced course
Excel Advanced - Formulas & Functions course

IF and VLOOKUP

Hi,

I am trying to build a spread sheet to import into the accounting software.

I have a list of tenants and I need to populate the “Property references” on another column using “IF” and/or £VLOOKUP” and/or “LEFT” functions.
COLUMN “A”
18 Amherst Road:13/14 Christopher Elms
122 River Road:13/14 Andrew Eady
169 Lenton Mount:13/14 Jack Beattie
TABLE
18 Amherst Road 12AR.F1
169 Lenton Mount 169LM.F1
122 River Road 122RR.F1
I need to write a formula to say if “Column A” starts with 12 Amherst Road, it should find the corresponding reference for that from the TABLE.

Is this possible?

Thanks for your help In advance!

Fri 9 Aug 2013, 09:14 Trainer Rodney said...

RE: IF and VLOOKUP

Hello Mathanki,

Hope you enjoyed your Microsoft Excel Advanced courses with Best STL.

Thank you for your question regarding IF and VLOOKUP.

It is very difficult to work with data in Excel when there are a number of data parts in one cell. What I suggest is that the data is split into several columns and then you can use an INDEX with MATCH function to produce the reference.

I have attached a file which shows how this has been done.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer