Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

vlook up

ResolvedVersion 2003

Susan has attended:
Excel Introduction course
Excel Intermediate course
Excel Advanced course

VLook UP

Please can you assist.
When creating a VLook Up query I get a N/A unless I remove the "false" in the formula. However, if I do remove it the data is incorrect.

RE: VLook UP

Hello Susan

Thank you for your question.

The 'false' part of the vlookup function tells Excel that there needs to be an exact match to the lookup value (first part of the vlookup function), otherwise you will get an N/A result.

For example, if I am using the number 5 as my lookup value, and there is no number 5 in the leftmost column of my table array (second part of the function), then Excel displays N/A as my lookup value doesn't exist.

If I remove the 'false' from the function, then you are telling Excel that it only needs to find an approximate match to my lookup value (i.e. 'near enough is good enough').

Using the previous example, if I removed the 'false' and Excel found the number 4 in the leftmost column of the table array, it would then pull out the corresponding piece of data from the same row that the number 4 appears in.

I hope this helps to explain what is happening. Let me know if you have any further questions.

Amanda

RE: VLook UP

Thank you for your explaination.
How do I resolve this?

RE: VLook UP

Hi Susan

What would you like to happen if Excel returns an N/A error? Leave the cell blank instead of displaying NA?

Amanda

RE: VLook UP

Hi Amanda
I really not that bothered if it displays N/A or leaves a blank when there is no corresponding data to pull. I just need to be sure that the data it does pull across is correct.
I can send an example spreadsheet if that helps.

RE: VLook UP

Hi Susan

Entering false into the last part of the function will ensure that the correct data is pulled out. If what you are asking Excel to find doesn't exist in the leftmost column of the table array you have specified (second part of the function), then the N/A error will display.

I hope this helps.
Amanda

Wed 14 Jan 2009: Automatically marked as resolved.

Excel tip:

Move data worksheet to worksheet

To move data from one worksheet to another, highlight the data.

Select and hold down the ALT key and position the mouse on the border of the selection until the mouse pointer displays four-headed arrows.

Drag the selection down to the destination worksheet tab.

When the arrow touches the tab, Excel switches to the desired worksheet. Now drag the selection to the correct position. Let go of the mouse and then the ALT key.

To copy data from one worksheet to another, select and hold down the CTRL+ALT keystroke combination and perform the steps above.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.