if and vlookup

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Excel Training and help » IF and VLOOKUP

IF and VLOOKUP

resolvedResolved · Medium Priority · Version 2010

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!

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

Attached files...

Index_Match.xlsx

Fri 16 Aug 2013: Automatically marked as resolved.


 

Excel tip:

Repeat action

The F4 key will usually repeat your last action. e.g. delete a row, then select another row and press F4 to delete again.

View all Excel hints and tips


Server loaded in 0.12 secs.