I have a table its in a worksheet called RMs.

The table is made from a query of a sharepoint list, which is a raw materials database held on SP.

On another sheet a user can enter a raw material code which is searched for using vlookup on the Sharepoint query table in the RMs worksheet. The Numbers is in the first column of the table and the user entered cell are formatted as a number.

When I test the match using the formula =$C7=RMs!$A8 which should be a match I get a FALSE.

When I test it with =$C7=VALUE(RMs!$A8) I get TRUE.

Both cell formats are set to number with no decimals.

I have no idea what is causing the format issue here other than perhaps the data in the table is seen differently by the Vlookup function.

The Vlookup formula is…..

=IF(ISERROR(VLOOKUP($C9,RMs!$A$4:$J$2000,1,FALSE)),,VLOOKUP($C9,RMs!$A$4:$J$2000,2,FALSE))

First error checks that there is a code in the first column, then if there is, gets the name of the ingredient from the second column in the lookup.

Any Ideas

​I have a table its in a worksheet called RMs. The table is made from a query of a sharepoint list, which is a raw materials database held on SP. On another sheet a user can enter a raw material code which is searched for using vlookup on the Sharepoint query table in the RMs worksheet. The Numbers is in the first column of the table and the user entered cell are formatted as a number. When I test the match using the formula =$C7=RMs!$A8 which should be a match I get a FALSE. When I test it with =$C7=VALUE(RMs!$A8) I get TRUE. Both cell formats are set to number with no decimals. I have no idea what is causing the format issue here other than perhaps the data in the table is seen differently by the Vlookup function. The Vlookup formula is…..=IF(ISERROR(VLOOKUP($C9,RMs!$A$4:$J$2000,1,FALSE)),,VLOOKUP($C9,RMs!$A$4:$J$2000,2,FALSE)) First error checks that there is a code in the first column, then if there is, gets the name of the ingredient from the second column in the lookup. Any Ideas   Read More

​