vlookup "FALSE" returns 0 instead of cell value for some rows

user_18972482 0
Viewed 74

[Cells that I am retrieving information from: Stranding log full_YS oh crap.xlsx]Infostats - sites'!$H:$J) (Site= Gamtoos River Mouth; Lat= 33°58'03.1"S, Long= 25°02'47.8"E)]1

[Row 1: Site= Gamtoos River Mouth; Long: =VLOOKUP($Q58;'[Stranding log full_YS oh crap.xlsx]Infostats - sites'!$H:$J;2;0) (returns 0); Lat: =VLOOKUP($Q58;'[Stranding log full_YS oh crap.xlsx]Infostats - sites'!$H:$J;3;0) (returns 0) Row 2: VLOOKUP($Q59;'[Stranding log full_YS oh crap.xlsx]Infostats - sites'!$H:$J;2;0) (returns corresponding value of cell in Stranding log full_YS oh crap.xlsx]Infostats - sites'!$H:$J)]2

I'm using the vlookup function to retrieve GPS locations from a different document. Image 1 contains the cells I'm retrieving information from (Site= Gamtoos River Mouth; Lat= 33°58'03.1"S, Long= 25°02'47.8"E). The second image shows the formula that I'm using and 2 rows. The 1st row has the information from image 1 (Site= Gamtoos River Mouth) and I want to get the GPS locations in the Lat and Long columns. The second shows how the formula worked for another row.

Function: Long =VLOOKUP($Q58;'[Stranding log full_YS oh crap.xlsx]Infostats - sites'!$H:$J;2;0) Lat =VLOOKUP($Q58;'[Stranding log full_YS oh crap.xlsx]Infostats - sites'!$H:$J;3;0)

I would like to know why the formula is working for some rows but not others and how to fix it?

I'm not sure how to add images yet, sorry.

Thanks!

vlookup return-value excel-formula
1 Answers

If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, take time to verify that the referenced cells don't have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type.

You could try making at new data set in notepad og notepad++. import the data in a new excel sheet with the import guide in the data tab, make sure to set the correct data types for the coordinates. see if this solves the issue.

Another thing you could do is to use the =TRIM and =CLEAN functions on the data to debug the error.

user_133400470
Related