vlookup troubleshoot

[SthavyaK]

Dear Formula Guru, 

 

I have a worksheet "Camlins UGF Area" which calculates the values automatically. It is a dynamic worksheet and uses the record format as a criteria. I have another worksheet "Test" which has mostly static data but column C gets the input from "Camlins UGF Area" Column B. The snapshot below shows the formula I have been trying to use, which is - Vlookup(find value in A2, column to pull the value from, value if not found, worksheet to refer to). The value not found is showing up despite having values.

 

Please tell me what is wrong in the formula. I have been trying different solutions for past 2 days but no luck yet. 

 

 

Regards,

Sthavya

Beginner @ worksheets

[Pat Stanford]

It would be much easier if you would post a sample file. Very hard to read the small print on the attached PDFs.

 

I think you need 

 

=VLookup(A2, 1, 0, 'Comlins UGF Area':A2..B2);

 But upon actually trying this, it appears that Lookup does not work and Dataabses, only on Spreadsheet Cells.

 

I hope someone will tell me I am wrong and how to make it work.

 

😞

 

[SthavyaK]

18 hours ago, Pat Stanford said:

It would be much easier if you would post a sample file. Very hard to read the small print on the attached PDFs.

 

I think you need 

 

=VLookup(A2, 1, 0, 'Comlins UGF Area':A2..B2);

 But upon actually trying this, it appears that Lookup does not work and Dataabses, only on Spreadsheet Cells.

 

I hope someone will tell me I am wrong and how to make it work.

 

😞

 

Thanks @Pat Stanford but seems like it is not the right formula. I am not sure where it is wrong. We are doing everything by the book. I wish ChatGPT could assist with this. Would be nice. 

[SthavyaK]

UGF Test file.vwx

 

Please find attached the test file.

[Pat Stanford]

As I said in an earlier post, I don't think VLookup will work on Database Rows.  But that would be a good thing to add to a wish list.

 

If you make spreadsheet rows with all the possible 'Surface Cover Type' values, you can then use the criteria in the Area function to get the values you have in the database. Take a look at what I did in column C, Rows 5-21 of the Camlins worksheet.

 

=AREA('Camlins UGF'.'Surface Cover Type'=A5)

 

This effectively creates the same criteria as in your summarized database rows, but in a spreadsheet cell.

 

Also note that VLOOKUP requires an EXACT Match.  I have deleted extra spaces from the end of some of the cells in either the Test or Camlins worksheet.  There is still a space at the end of Hedges in row 12 of Camlins. The dash in Greenwall is different between the two worksheet. I expect that you have similar "typo" issues in the other cells that are not reporting correctly.

 

HTH.

 

 

PAT UGF Test file.vwx

[SthavyaK]

Thank you for that @Pat Stanford. This is very helpful.