lookup and match

%3CLINGO-SUB%20id%3D%22lingo-sub-2556472%22%20slang%3D%22en-US%22%3Elookup%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556472%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20program%20written%20to%20vlookup%20names%20and%20match%20information%20for%20golf%20league%20payouts.%20The%20program%20used%20to%20pull%20this%20information%20changed%20the%20format.%20How%20do%20I%20lookup%20a%20name%20(Ron%20Worrich)%20on%20a%20different%20worksheet%20formatted%20as%20Ron%20Worrich%20(7)%20to%20pull%20the%20information%20needed%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2556472%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2556581%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2556581%22%20slang%3D%22en-US%22%3Ebasically%20I%20would%20try%20to%20clip%20the%20lookup%20range%20based%20the%20length%20of%20the%20lookup%20value.%20Something%20like%20this%3A%3CBR%20%2F%3E%3DXLOOKUP(A1%2CLEFT(OtherSheet!%24A%241%3A%24A%24999%2CLEN(A1))%2COtherSheet!%24B%241%3A%24B%24999%2C%22not%20found%22))%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2557389%22%20slang%3D%22en-US%22%3ERe%3A%20lookup%20and%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2557389%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1105439%22%20target%3D%22_blank%22%3E%40rworrich%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EXLOOKUP()%20works%20with%20wildcards%2C%205th%20parameter%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20622px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F296481i7A70BE92A0900BD6%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I have a program written to vlookup names and match information for golf league payouts. The program used to pull this information changed the format. How do I lookup a name (Ron Worrich) on a different worksheet formatted as Ron Worrich (7) to pull the information needed?

 

4 Replies
basically I would try to clip the lookup range based the length of the lookup value. Something like this:
=XLOOKUP(A1,LEFT(OtherSheet!$A$1:$A$999,LEN(A1)),OtherSheet!$B$1:$B$999,"not found"))

@rworrich 

XLOOKUP() works with wildcards, 5th parameter:

image.png

@Sergei Baklan, worked great thanks. Now, how do I not count or eliminate  Bl[Last, Name] from the below list?

Worrich, Ron

Sampson, Ralph

Jordon, Michael

Bl[Sampson, Ralph]

 

Russell, Bill

Bl[Harper, Bryce]

Tatis, Fernando

DeGrom, Jacob

 

 

  

@rworrich 

that could be

=XLOOKUP("*" & Name & "*", ...)