How to use PROCV to search for codes in two spreadsheets?
In Excel 365, version 1907, and Windows 10, I have an XLSX file with two sheets almost equal-worksheet 1 and worksheet 2.
Table Content1:
Table Content2:
I want to check on Floor2 if the "doc" numbers exist on Floor1
So I did it like this in a new column on the Floor2:
=SEERRO(PROCV(B2;Planilha1!A$2:B$5;2;FALSO); "Não")
But all results appear as " no " despite three codes in" doc " exist in the two sheets
Please, does anyone know what might be wrong?
1 answers
You are using the references and the wrong formula, see the result in the image below.
1) Consider being with worksheet 2 Active, which you want to put the formula in cell C3;
2) you start by writing in C3 " = SEERRO (PROCV (", select in Sheet 2 cell B3, as you want to start your search from that cell;
3) go to Sheet 1 and select the entire column B, as this is where the searched data is. At this time the formula will stay like this: "= SEERRO (PROCV (B3; Planilha1!B: B;'...
4) notice that you selected only Column B in Sheet 1, so your index starts at 1 (you wrote 2 in your formula).
5) so then continue writing your formula which should look like this:
=SEERRO(PROCV(B3;Planilha1!B:B;1;FALSO); "Não")