Vlookup

[vino3007]

Hi,

Am trying to do a vlookup Sheet1(MIS) A2 with Sheet2 (Working) C2:C5 and return value available in M,N,O,P,Q to Sheet 1 (MIS) at J,K,L,M,N. Since there are some #N/A available in sheet 2(Working) it is returning as #N/A in sheet 1(MIS) also. Please help in solving this issue.

I have attached the sample data for reference.

Warm regards,
Vinoth

[Mallycat]

Your formula is all wrong

=VLOOKUP(A8,Working!C2:C403,10,0)

the value A8 is current pointing at an empty cell. This is the value you should be looking up. I assume this should be cell A2
C2:C404 is the look up range, but this is a single column. Vlookup is designed to look up a table with multiple columns. The next value (10 in our forumula) tells the Vlookup which column to use in the table. You you are saying look in column 10, but you only have 1 column.

Fix these problems and your issue will go away

[vino3007]

Hi,

Sorry i have corrected the formula, but now the returned value as "0", but there is a value as "SME" available in M4 which is not getting returned. Kindly help in fixing this, have attached sample data.

Regards,

[Mallycat]

OK, good progress. Now you should do the following.

1. Change your data on sheet "working" to a table. Click in the table and then select INSERT\TABLE. This will turn the table to an EXCEL TABLE. This has extra features that allows you to refer to the table rather than the range. When the table has new rows added, all your formulae will be updated automatically
2. Go and change your formula in cell J2 to point to the table. Go into edit mode, select the part of the formula "Working!C2:Q403", then press F5, and select the table you added in step 1. From now on, the formula will update when ever you add extra cells to the 'working' table.


Regarding your sample sheet, it has a lot of linked cells, so I can't check what is happening. Please save the data in the working table as values, then post again
Regarding the lookup, your sheet p

[vino3007]

Hi,

I have attached the revised file with all the reference data provided from where the data for Sheet (working) is being linked.

Please help in solving the issue, am not able to do change the sheet into table. Am not getting it can you please change in the attachment.

Warm regards,

[Mallycat]

Your vlookup is working correctly now. Here is how to format as a table

[vino3007]

Hi,

I have converted the sheet (MIS & Working) as Table, but still the am getting as 0 instead of SME. Can you please help me in solving this. Am using excel MS office professional edition 2003.

I have attached the sample file.

Warm regards,
vinoth

[Mallycat]

The reason you are getting 0 is that 0 is the right answer based on your current formula. Your formula is looking up the value "ADSORBENT CARBONS (P) LTD." This value can be found in cell B2 on sheet working. The 10th column (as determined by the 10 in your formula) is column L. Therefore the returned value is what is in cell L2. To prove it, change the value in cell L2 and see what happens.

[vino3007]

Hi,

Sorry i had made changes in the formula, actualy the value should be returned is matching A1 (MIS sheet) with C2 to C5 of (Working sheet). There would be three #N/A matching but formula should ignore that and should return value SME (as in M4 of working sheet).

Please suggest a formula for the same. I have attached the corrected sheet,

Warm regards,
Vinoth

[Mallycat]

There is nothing wrong with your vlookup. it is correctly returning the value in cell M2 which is now #N/A. If you want it to return SME, then you need to change the formula in cell M2 so that it shows SME and not #N/A. I have no idea what that formula does, as it is pointing to another sheet that I can't see.