Free Microsoft Excel 2013 Quick Reference

VLOOKUP formula won't work

OK, I give up. I am trying to get a cell to look at two items on the Physical Damage(sheet 1) page and pull from the PhysDmg (sheet 2)page. What am I doing wrong? Example: The sheet two page named PhysDmg shows in column A 11000 or 21000 or 31000. Column B would have the number I want returned. On sheet one named Physical Damage cell d12 would have a number like 1 or 2 or 3. Cell J6 would have the number 1000. All I am getting with my formula is N/A. I have used this same formula on other worksheets and it has worked fine. Please help. Thanks.

Post your answer or comment

comments powered by Disqus
im trying to get a vlookup formula work for some grades which converts the letter grades into a number. once i completed this i can then creat a graph from the number that the vlookup gives me however this is not working. i have attached the spreadsheet and the page you need to look at is the processing page. the cell is highlited in yellow to show the formula which is wrong i have another vlookup very similar which works however this one im tryna work just wont. Final system V8.1.xlsx

I have a worsheet that I cannot get a vlookup formula to work properly in - 4 columns - 1st column is a part # one of my vendors uses. 2nd column is my part number - 3rd column is a part # which may be found in column 3 - I want column 4 to read the cell in column 3, find the same value in column 1 and return the value in column 2 - example attached - any help is appreciated!



Me again with this age old problem. The vlookup formula is only partly working on the attached sheet, but I cant find an explanation as to why it can look up some criteria but not others.

I have to updated these prices on a monthly basis

Thanks in advance for your time


Hi Everyone,

I can't figure this one out - hope someone can tell me why this VLOOKUP is not pulling a result of #N/A:


- Column K contains the lookup value which is a number extracted from another column using a formula =MID(A188,4,5).
- The lookup range (table array) was on another tab but I moved it over to the same sheet where I have the VLOOKUP formula but this did not change the result.
- The value being looked up is in the first column of the range as well.

When I replace the cell reference with an actual value then I get the expected result.

Any help is much appreciated!
Thank you.

I'm having a hard time getting a custom vlookup formula to work.

I need to be able to change a part of the range in the vlookup dynamically based on a cell value.

This is what I came up with but it gives me a value error. Can anyone help?

Function VL(val As Variant, RNG As Range, col As Integer, BL As Boolean)
Dim Lookup As Range
Set Lookup = Range("'C:AirlineDataTotal O&D[" & RNG & " Total O&D.xls]by market'!$B$1:$D$65536")
VL = WorksheetFunction.VLookup(val, Lookup, col, BL)
End Function

Hello all,

I have some data with leading zeros that for that very reason i have to import as text format to Excel. This is part numbers and inventory on hand qty for a retail business.

My issue is - after i import the data I have a VLOOKUP formula that references that same data which will not work because of the text formatting. For some reason the text formatting does not allow the formula to read the data.

I can highlight that column after the import and change the format to general but it does not automatically update the formatting of the column from being text. And if it did, it would delete the leading zeros!

My formula errors until i highlight a cell, edit it manually (f2) and enter. This somehow updates / applies the general formatting and allows the formula to start working (but again, deletes the leading zeros in the process which causes me issues)

Why will my VLOOKUP formula not work with text formatting?? Is there a way around this?

My formula is as follows: =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),0)

I am entering the formula into the first sheet of a workbook, with the leading zero / text data in the Sheet2. (it is set up to return a 0 if there is an error) Format of the sheet is: A column = Part Number, B Column = Qty Available

Hope that explains it well enough. I though i knew Excel well, but this one stumped me good.

Anybody think they know the way around this one?

Thanks, Jay

I'm trying to do a very simple Vlookup formula, but the value isn't showing in the cell, the entire formula shows in the cell. What am I doing wrong?


i have a simple vlookup formula that works eg


but when there is nothin in c11 i get a nasty #n/a appear

so i created this formula


what can i put in the ?????? to have a blank cell

Hi all, I hope you guys can help me once again. I just upgraded to excel 2003 and I had excel 2000 but now my 1 of my formulas wont work. It comes up with name instead of total. I have attached it can anyone help if so I would greatly appreciate it.

Hi all

Has been a while since I have come unstuck and I hope my problem can be resolved...

I have used a formula in one worksheet uses two calulated values. One formula returns a value from another worksheet. The other formula is in the same worksheet in which a final value is being calculated. The final formula wont use the returned value from the second worksheet.

Am I missing something or is there some switch that needs activating or does excel not handle this sort of calculation?

Here are the formulas:

This formula (below) sits in Col AS in worksheet 1 and returns a value from Assumptions worksheet (worksheet 2).

=IF(ISERROR(INDEX(Assumptions!$E:$E,MATCH(AR10,Assumptions!$D:$D,0))),0,INDEX(Assumptions!$E:$E,MATC H(AR10,Assumptions!$D:$D,0)))

Also in worksheet 1 there are two other formulas:

One in Col AQ which is =IF(OR(H10="Y",H10="N"),AQ9+AN10,AQ9)

The result of this formula is used with the one above (in Col AS) in a second formula in worksheet 1 shown below and sits in

Col AT which is =IF(OR(H10="Y",H10="N"),AQ10+AS10,AQ9)

The formula in Col AT uses the value in Col AQ but not the value in Col AS which is the value returned from the second worksheet.

Is it something to do with values outside the active worksheet?

I look forward to your thoughts...

Many thanks in advance...


Hi guys,

Just wondering if someone could have a look at the attached workbook. I am trying to build a Holiday Booking spreadsheet and am using multiple VLookups (Not sure if its the most efficient, so that could be my problem). There are two Vlookup arrays (The first looks at the raw data and sorts it into days, the second looks at this and sorts into shifts) and the second VLookup will not display the result, just brings up "#NUM!". I have tested the search as a normal VLOOKUP and it works fine... Not sure what i am missing.

Any help on this would be much appreciated



hi everyone, just hoping someone would help me with this problem, basically my vlookup formula that I entered wont work (please see the example file), essentially I have numbers like the one in A3 which I got from concatenating two variables I obtained from one database.

In another workbook I would have rows (approx 200000 rows) of variables like the one in row 1. This I obtained from another database.

I then need to extract the data in second workbook to first workbook. I used vlookup which work for most identifiers but not all. any suggestions whats wrong and how to fix it ??

thank you.

Hi all.

I hope someone can help me...again!

I have a VLOOKUP formula, so when the cell above (C5) has a value, it returns the value from a defined list. Simple so far and it works great when I manually input the value in the cell (C5).

The problem occurs when I change C5 to contain an AVERAGE formula? Any ideas why it will only work when there is a plain old value in there, but not a formula?

This is the VLOOKUP formula I'm using:



I am trying to use the vlookup formula and i cant figure out why it wouldn't work,

Here is the formula I am using.
in A2
=VLOOKUP(F2,Sheet2!1:65536,2,0) and it gives me back a #N/A But when I say =VLOOKUP(F2,Sheet2!1:65536,2,1) it will return a wrong answer.

Did I do any thing wrong here?

Right, here is my formula


1 ID Name Name Code
2 111 A #N/A E XXX
3 222 B #N/A C UUU
4 333 C #N/A D GGG
5 444 D #N/A B BBB
6 555 E #N/A A NNN

I want to match the ID of column A, with the name located in columns B&Y - but each time I enter the formula above - it just returns 'N/A'.

To my mind's eye - this formula looks right & I can't work out what is wrong!

Any ideas?



I have a worksheet which uses data validation in one column (Column A) to
select a customer name and then a VLOOKUP formula in the next column (Column
B) to return the corresponding customer number. This was working just fine
but within the past 30 minutes, when I choose a customer name in Column A,
the B value is not returned until I either save the spreadsheet or click on
the formula in the formula bar and then press enter. I did not change the
formula in either A or B.

Does anyone have any experience with this or know of a workaround? The
spreadsheet also uses INDEX and MATCH elsewhere with a lot of tables (which
are not used in the above scenario) and so I'm reluctant to recreate this
from scratch.


I have a data validation list which returns a number to G49 from a list through VLOOKUP. If I pick the first value in the data validation list, then number "1" will be returned to cell G49. If i pick the second value it will return "2" to cell G49.

The VBA below is designed to run a macro depending on the value in G49 but it wont run. If i overwrite the VLOOKUP formula by typing "1" or "2" in G49 the macro runs. Can someone tell me whats wrong with the code?

    If Target.Address = "$G$49" Then 
        Select Case Target.Value 
        Case Is = 1: Call Deponi 
        Case Is = 2: Call Dæk 
        Case Is = 3: Call Fyldplads 
        Case Is = 4: Call Gips 
        Case Is = 5: Call Jernogmetal 
        Case Is = 6: Call PVC 
        Case Is = 7: Call Madrasser 
        Case Is = 8: Call Stortbb 
        Case Is = 9: Call Stød 
        Case Is = 10: Call Trykimp 
        Case Else: Call Deponi 
        End Select 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I'm running against this problem: a file should be run whenever people want.
however, the last part to complete the file is that the file from yesterday should be opened.
From this file data will be extracted via the VLookup formula...that's the plan.
However, with dim statements it won't work.
for now the code is:

 mynum, mysearch, this1, that1, other1 
 'Open the file from yesterday. Define the date string of this week and last week
mysearch = Left(mynum, 8) & Right(mynum, 2) - 1 'mynum is a date string like 2008-06-26
this1 = "Outstanding invoices " & mynum & ".xls" 
that1 = "Outstanding invoices " & mysearch & ".xls" 
Set other1 = Workbooks(that1).Sheets("Raw").Range("comments") 
ChDir "Z:FINANCEF&AAPInvoices overdue" 
Workbooks.Open Filename:= _ 
"Z:FINANCEF&AAPInvoices overdue" & that1 
 'Search for comments last week from last week's file
With Range("P2") 
    Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).Select 
    Selection.FormulaR1C1 = _ 
    Selection.Value = Selection.Value 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the "this1, that1" etc appear when you point with your mouse in the code as correct but
the VLookup function won't work: it takes a long time and when interupted the macro the cells show the VLookup formula with "[that1]" instead of the workbook's name.
I also tried to dim that1 as workbook but that did not help.

Obviously I'm doing something wrong.
I appreciate your help with this.
Thanks in advance.

I am using Vlookup formula which is working fine. I want make the "2" in this formula relative, so that when I drag it to the adjacent cell (to the right) the "2" becomes a "3". If I repeat, the "3" becomes a "4". Is there an easy way of doing this?


Thank you,


Is it possible to use a wildcard/joker in the second part (table array) of the Vlookup formula? Am trying to incorporate and analyze weekly shoppinglists from various supermarkets in a program but, due to them all using various names for the same items (i.e. bread, brown bread, white bread etc), my table array list is getting rather long and slowing up the works. I am using Excel2000(UK).
Thanks in anticipation for the trouble.

I was given the following formula by Domenic some time ago that I use along with VBA code supplied by Fin Fan Foom to open do a lookup on a closed workbook:


Hi all,

I have a workbook with two sheets: orders and products. In column "O" of the orders sheet, I need a VLOOKUP formula to be inserted in every cell. Here is what I would like to have in O2:


And here is what I would like in O3:



Here is the VBA code I am using to insert this formula:

For counter = 2 To numRows
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],products!A2:C488,2,FALSE)"
    ActiveCell.Offset(1, 0).Select
Next counter
However, when it inserts the formula in O2, it throws in some extra characters. Here is what ends up in O2:


If I delete out (by hand) those four extra apostrophes, the formula works. Does anyone know how I can fix my code so that those four apostrophes don't appear in the resulting formula?

Please forgive me if I am making this code too complicated or completely screwing it up -- I am just teaching myself VBA using random websites



I hope somebody could help me with the formula Im using to calculate the management adjustment..

The following formula gives a #VALUE! error..

=(IF($C$2="P9",VLOOKUP($A$2,'Forcast P9'!$A$2:$W$32,2,FALSE)-B76)),(IF($C$2="P10",VLOOKUP($A$2,'Forcast P10'!$A$2:$W$32,2,FALSE)-C76),(IF($C$2="P11",VLOOKUP($A$2,'Forcast P11'!$A$2:$W$32,2,FALSE)-D76)),(IF($C$2="P12",VLOOKUP($A$2,'Forcast P12'!$A$2:$W$32,2,FALSE)-E76)))

C2= the periods (like P9, P10 etc..)
A2= The divisions..(also the total division)
Forcast P9= This is the mastersheet with the forcast on division level..
B76 etc..= This is the new forcast on total level for period P9, P10 etc...

The formula did work for only getting the right Forecast, but that was without the "minus B76" ..Is it possible to calculate the adjustment with this formula..?

A co-worker brought me an Excel workbook that all the formulas stopped working.

Simple things like A1*B3/C3 for example no longer work.

You can change the numbers in any of these cells and nothing calculates and then even more bizzare is when you re-write the formula the calculation will work and show the correct answer, but then that answer is now stuck in that cell and wont change even if you change any of the variables that are invovled in the answer.

Is there some obscure Excel setting or option that does this?

Thank you

Can you tell me why this formula isn't working I am trying to use this VLOOKUP formula and its just giving me the #N/A I am not sure why

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB1B2=
ABCD1884838#N/A 2884474#N/A Sheet1
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

No luck finding an answer? You could always try Google.