• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP formula doesn't return data in cells when it should

A

Andrew Tertes

New Member
I'm using the following formula to do a VLOOKUP in order to lookup numbers from worksheet1, column F with column A of worksheet2, and then to populate numbers from worksheet2, Column 2 to the active worksheet1's cell with the formula.


Every month I have a new Worksheet1, and frequently the formula doesn't return data in cells even when the same numbers appear on both worksheets. For example:


If the active worksheet1 has 44004 in cell F2, and on worksheet2 44004 is in cell A2, I want the data from worksheet2 Column 2 to appear in the cell with the formula. However, I'm getting a blank cell. If I don't use the IFERROR formula, I get #N/A.


=IFERROR(VLOOKUP($F2,'Worksheet2'!$A$2:$AJ$81,2,FALSE),"")


The cell's format is "General".


How can I have the data I want to appear?
 
@Andrew


Hi


Firstly Welcome to Chandoo,org Forum, glad you are here


you want to lookup the values from the Sheet1 in sheet2 but your formula is


=IFERROR(VLOOKUP($F2,'Worksheet2'!$A$2:$AJ$81,2,FALSE),"")


but your data is entered in Worksheet1 so i think you must change formula to


=IFERROR(VLOOKUP($A2,'Worksheet1'!$F$2:$J$81,2,false),"")


OR


=IFERROR(VLOOKUP($A2,'Worksheet2'!$A$2:$AJ$81,2,FALSE),"")


i confused is it possible to upload a sample file


Thanks


SP
 
Hi Andrew, can you post a sample workbook...


http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Hi SP. Thank you for your welcome and reply.


I need to correct myself: my data is in worksheet2.


Per Indian's request above about posting a sample workbook, I revised my spreadsheet to get it ready to post. When I did, now there are some correct values in the formula cells, column G, while other cells are still blank.


Indian, here's the link for the sample workbooks: https://www.dropbox.com/s/x7dfnbiiyoxyoz7/sample1.xlsx


SP and Indian, I look forward to your recommendations.


Regards,


Andrew
 
@Andrew


Hi


i download your file and i check the formula there is no wrong in that formula but in worksheet1 you have some error code is display from the range F36: so when i press the f2 button in that range and press enter then i got the lookup value in the Column G


Please check if the problem is still in your side then let us know and we will try


Thanks


SP
 
Hi SP,


Wonderful. I clicked F2 in column F, and data correctly appeared in column g.


Thank you for your help!


Regards,


Andrew
 
Hi Andrew Tertes


The values in F36:F81 are text, copy a blank cell, select F36:F81, Paste Special, Add. that will convert the values to numbers. F2 & F3 are not in the list!


Kevin
 
This is a follow-up to the above issue. I've run into the same issue a number of times since I originally posted.


Usually clicking the F2 in the affected cells clears up the issue.


Today, that procedure did not give me my desired result.


Ultimately, I saved the file to .csv, which worked.


Andrew
 
Hi Andrew,


When clicking the F2 in the affected cells doesn't clear up the issue, You can perhaps 'Clear Formats' in the affected cells and click F2.


I have seen this issue occurs for some of the BW or SAP downalods.
 
You must log in or register to reply here.
Back
Top