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

vlookup function

ResolvedVersion 365

Cameron has attended:
Excel Advanced course

VLOOKUP Function

Hello,

I need a little bit of help with a VLOOKUP.

Is it possible to run a VLOOKUP function on a list of data and if the 'lookup value' is not found in the source data, instead of returning '#N/A' the value remains as it was originally?

Does this make sense?

Many thanks,
Cameron

RE: VLOOKUP Function

Hi Cameron,

It sounds like you need a formula to swap out the #N/A when you get it.

You might need the IFNA function

=IFNA(Value,Alternative if there is an #N/A error)

That would look like this if A1 is your lookup value

=IFNA(VLOOKUP(A1,Table Array,Col Num,FALSE),A1)

Does that help?


Kind regards

Richard Bailey
Microsoft Certified Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 6 Mar 2017: Automatically marked as resolved.

Excel tip:

Conditional Formatting in Excel 2010

If you have lots of data in a spreadsheet, you may find that it is easier to read if you highlight some of the values. This is Conditional Formatting and here's how to use it:

1) Select the data you wish to apply the format to and click Conditional Formatting
2) A list of options will then appear, from this list, choose the format you wish to display e.g. find all cells with a value less than 0
3) Excel will then highlight all of these cells

To remove this: select the highlighted cells, click the drop down on the Conditional Formatting icon and select Clear Rules from selected cells.

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.19 secs.