This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
have excel sheet having three worksheets Sheet1,Send1,Send2
Sheet1 has columns Name,Sex,Mobile and other fields,
in sheet sent1 columns
i want to search for each row in sheet1 column c2 mobile numbers in sent1 and get corrosponding logstaus i.e F2 column value. i tried vlookup and xlookup but both case i am not getting answer , getting #N/A, can any body suggest what wrong i am doing. the excel file can be downloaded from belwo link https://docs.google.com/spreadsheets/d/1LhXxmLdy8tWEi3O9kRgBvPw-9L8GFPVm/edit?usp=drive_link&ouid=101069295157692527186&rtpof=true&sd=true || | -------- |
Hi,
Please change the formula in Sheet 1 column C and C2 to:
=VLOOKUP(C2,Sent1!$C$1:$E$35,3,0)
The range you select should be start from the mobile column but not the Name column, because you are trying to look for the Mobile column in Sheet1.
If the response is helpful, please click "Accept Answer" and upvote it. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Thanks for your valuable time. but still the sheet is not showing the Logstatus from Sent1. i have changed the formua the way you suggested. also changed the column in sent1 by moving it to from column A to C location. kindly have a look in modified file https://docs.google.com/spreadsheets/d/11oDHvlbxzSsOk7irDT4kwgxdmsb709_P/edit?usp=sharing&ouid=101069295157692527186&rtpof=true&sd=true but still the vlookup is not working correctly
Hi @Sushil Agarwal ,
This formula worked in Excel for Desktop app:
Did you try to check if the formula works in Excel for desktop?
And I cannot download the file to check if this problem related to your Workbook. I suggest you check if the Mobile columns in different Worksheets are all set to Number format or Text format, make sure 2 Columns are in the same cell format.
Thanks, it works now, the cell format was mismatched
It's glad to help you! :D
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(44.800000000000004, 28.999999999999996%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAZ%3C/text%3E%3C/svg%3E)
Hi
it is completely expected behaviour
https://support.microsoft.com/en-au/office/how-to-correct-a-n-a-error-in-the-vlookup-function-e037d763-ffc3-4fae-a909-89c482d389b2
When the range_lookup argument is FALSE—and VLOOKUP is unable to find an exact match in your data—it returns the #N/A error.
Try workaround at this page and pay attention that formula for row number 4 starts to looukup on row number 3 (not 2) in the file provided by you
=VLOOKUP(C4,Sent1!A3:E37,5,0)
=VLOOKUP(Sheet1!C2,Sent1!A2:E35,5,0) i am looking Sheet1!c2:C55 values in Sent1!A2:A35 i.e. mobile number from sheet1 , C columns in sheet Sent1 A2:A35 (Mobile) column, and get the corrospoding row E column (Logstatus Value).the mobile number 9225527126 in c4 is there in Sent1, A2 column , i want it to return me corrosponding E2 value i.e.