VLOOKUP function Showing wrong result

[Ramadan]

I have a Workbook with 9 sheets all of them have tables with same design and format with different names and data, in cell "B2" there is a code for each sheet. What I need is to nest a VLOOKUP function in "C4" to search across all the sheets for a name or phone number and if there is a match to give me only the sheet code that has the matching information in Cell "B2" and I need to nest another function in the next row based on the result in "C4" to let me know the row number in the table that has that match

I have created a part of the VLOOPUP function combined with IFERROR but as you can see in the sxcreenshots below the name that I search for it is found in sheet2 "Carnel" in cell "G10" but the function in "C4" result gives me TRUE while it should give the result as "NH02" because the name is found in this sheet and also in the function I wrote if no data found to give me "No Match"

attached image 1 the sheet that has the data I'm searching for and image 2 the sheet that has to formula

I need your help please

 

[Kaper]

You use wrong arguments in VLOOKUP the third argument shall be a number of column, the 4th (optional, and missing in your formula) shall be set to False (or just 0), because you need exact match,

 

[Kaper]

PS. It returns TRUE because you have iferror(first wrong VLOOKUP) =iferror(second wrong VLOOKUP) , so
No Match=No Match
And this is of course TRUE.

To test the formula, and how each formula argument is interpreted, you can select a cell with a formula and use Formulas->Evaluate formula tool

 

[Ramadan]

You use wrong arguments in VLOOKUP the third argument shall be a number of column, the 4th (optional, and missing in your formula) shall be set to False (or just 0), because you need exact match,

=IFERROR(VLOOKUP($D$3,Carnell[#All],'Carnell'!$B$2),"No Match")=IFERROR(VLOOKUP($D$3,Ivory[#All],Ivory!$B$2),"No Match")

@Kaper Thank you for your usual help. this is my formula (lookup value is "D3" and table array is all columns and (column index _num) I put "B2" because it's only one cell. if you mean this is the error, How should it be?

 

[Ramadan]

PS. It returns TRUE because you have iferror(first wrong VLOOKUP) =iferror(second wrong VLOOKUP) , so
No Match=No Match
And this is of course TRUE.

To test the formula, and how each formula argument is interpreted, you can select a cell with a formula and use Formulas->Evaluate formula tool

View attachment 122641

PS. It returns TRUE because you have iferror(first wrong VLOOKUP) =iferror(second wrong VLOOKUP) , so
No Match=No Match
And this is of course TRUE.

To test the formula, and how each formula argument is interpreted, you can select a cell with a formula and use Formulas->Evaluate formula tool

View attachment 122641@Kaper I did but honestly don't know how to fix that

 

[Kaper]

Moreover, VLOOKUP uses a first column of the second argument as the column where first argument is searched. I don't think it is true in your case.

As you are using Excel 2021, use XLOOKUP instead of VLOOKUP.

XLOOKUP function - Microsoft Support

Use the XLOOKUP function when you need to find things in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. With XLOOKUP, you can look in one column for a search term, and return a result from...

support.microsoft.com

And looking on a better way to help - try installing XL2BB - Excel Range to BBCode
Working with spreadsheet is much better than with a screenshot.