Vlookups on formulas

The UK's most regular instructor-led training courses.
Training information: excel software training · Excel course london · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · Low Priority · Version 2003

3 posts

ReplyFri 29 Jan 2010, 10:57Delegate Christina said...

Christina has attended:
Excel Advanced course

Vlookups on formulas

Hello,

I have done the following formula =LEFT(B17,3), and then I want to do a Vlookup on the results of the above formula to then pull through the corresponding results. For some reason the results come through as #N/A.

Kind regards,

C Christoforou

Version	Date	Location	Places available	Book	Next place rate (£)
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2007	Mon 22 Mar 2010	Limehouse	3		£224	£225
2003	Tue 23 Mar 2010	Southwark	4		£207	£212
2007	Thu 25 Mar 2010	Rochester (Hoo)	0	FULL
2003	Fri 26 Mar 2010	Limehouse	4		£224	£225
2007	Tue 30 Mar 2010	Southwark	5		£199	£204
2003	Thu 1 Apr 2010	Bloomsbury	4		£207	£212
Full Schedule: See all 77 Excel Intermediate course dates. Bookings currently available until 23rd December 2010.

648 posts

ReplyFri 29 Jan 2010, 16:40Trainer Simon said...

RE: Vlookups on formulas

Hi Christina,

Thank you for your question.

Can you please tell me what the data is in cell B17.

I assume you are trying to take the first three letters from the text like a country code(Aus).

Is the result of the left formula returning correctly? If so are you using the result as your Lookup Value? When you create a Vlookup formula your Lookup Value must be the value that is contained in the first column of your data.

Let me know if this works.

Regards

Simon

3 posts

ReplyFri 29 Jan 2010, 16:45Delegate Christina said...

RE: Vlookups on formulas

Hi Simon,

Thank you for your quick response. Cell B17 contains "213 London" therefore I am extracting just "213" which comes through correctly. My vlookup table array has 2 columns:

1st being the column with 213 in it
2nd being a corresponding code number eg: 6

Kind regards,

Christina

648 posts

ReplyFri 29 Jan 2010, 16:58Trainer Simon said...

RE: Vlookups on formulas

Hi Christina,

Thank you for your response.

If your 213 was in cell A1 and the 6 in cell B1 and in cell C1 you type the value you are looking for in the table, then does your formula reflect the one below:

=vlookup(C1,A1:B1,2,FALSE)

Have a good weekend.

Regards

Simon

· Advanced Excel Training - takes you to the next level
· 3 Reasons why Your Small Business Office Needs Microsoft Excel Training
· Streamlining Excel To Save Time
· Tips From Excel's 'Rarely Used' File
· Where Is Your Business Heading? Let Excel Help You To Understand!

Please browse our web site to find out more about
excel software training and other Microsoft training courses.

Excel tip:

Transpose text

You can transpose any range of cells, turning the columns into rows and the rows into columns. Just follow these steps:

Select the range.

Click the Copy button on the Standard toolbar to copy it to the Clipboard.

Select a cell outside of the range you copied.

Select Paste Special from the Edit menu.

In the Paste Special dialog box, click Transpose, then OK.

View all Excel hints and tips