LinkBack URL
About LinkBacks
I have a list locate on Sheet4. On Sheet1 & Sheet2, I want to insert a formula in column I that will reference the list on Sheet4, A1:A58, check for a match, and return a yes or no if there is a match. What formula would I use?
I have a list locate on Sheet4. On Sheet1 & Sheet2, I want to insert a formula in column I that will reference the list on Sheet4, A1:A58, check for a match, and return a yes or no if there is a match. What formula would I use?
You can test out something like this:
=IF(MATCH(Sheet1!A1,Sheet4!A:A,0)>0,"Yes","No")
Regards,
Rudi
Hi,
One way
Formula:
Please Login or Register to view this content.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Sorry, forgot to mention, that it doesn't have to be an exact match. I'm matching to a list from another company, and our stuff is close but not exact. For example, they may have a position as Facilities Maintenance Engineer, and we just have it as Maintenance Engineer.
That makes it very tough indeed. Where do you draw the line in determining what is a match or not. These LOOKUP formulas are best used with exact matches, using unique values like ID numbers or Emp. Codes etc. They do have a close match option, but this is actually intended for numbers...close matches higher than the lookup value or lower than the lookup value. When it comes to Text (close match)...there is no guarantee what will be matched. Is there no other common field that you can use, like a record ID or employee code or even combining two fields to create a custom lookup value?
Hmm, let me think upon that. Perhaps I can modify it to match, and then use the formula.
Thanks!
When I used this, it returned #N/A for positions I know have a match.Originally Posted by RudiS
When I used this one, it returned a No for positions I know are a match. So I switched the No/Yes. Then it returned a Yes for a position that isn't a match. So not sure what's happening.
Would it be possible to upload a sample (desensitized) workbook with a portion of this list?
It will make it easier to assist.
N/A's can occur even if the formula syntax is correct, but no match is found due to leading/trailing spaces and other scenarios.
TX.
Here's it is... Test Match.xlsx
Hi
It seems you were wanting to look up the sheet 1 column C values. You didn't specifically mention column C so I used the generally accepted default example value of A1 in my original.
Even if you had used column C however it still would not have worked since your column C strings all have a trailing space character, i.e. "Beverage Steward ". It's always better with these sorts of things to make sure you have 'clean' data.
Try
Formula:Please Login or Register to view this content.
which incorporates a TRIM() function to strip out the trailing space
See also attached column E.
This works.Try
Formula:Please Login or Register to view this content.
which incorporates a TRIM() function to strip out the trailing space
Thanks!
TX Richard. I went out for dinner.
Glad you came right Jenn
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks