VLOOKUP function Showing wrong result

Ramadan

Board Regular
Joined
Jan 20, 2024
Messages
147
Office Version
  1. 2021
Platform
  1. Windows
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
1.png
2.png
 
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,
 
Upvote 0
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

1740304560393.png
 
Upvote 0
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?
 
Last edited:
Upvote 0
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 :)
 
Upvote 0
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.


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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top