Look up, Match question
I have a file with a column D "MR/NAMEA/28"
I need to output their gender by Look up or match from a list:
On the list are names with MALE as gender.
I need to have an output that when a string on Column "NAME" matched any on the column "DETAILS", it will output "GENDER".
Those who do not satisfy the criteria, will output a defined STRING.
please see attached
TIA
Capture.JPG
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Re: Look up, Match question
Please see attached file for the look up source and the before and after.
Thank you
Formula for B2 and fill down
Formula:
=IF(ISNA(VLOOKUP(MID(A2,4,FIND("/",A2,4)-5),'LOOK UP'!A1:B2000,2)),"Not Found","MALE")
Maybe like this?
=IFERROR(LOOKUP(2,-SEARCH('LOOK UP'!$A$2:$A$7,'RAW - AFTER'!A2),'LOOK UP'!$B$2:$B$7),"FEMALE")
If all listed names are "MALE", then this in B2, copied down:
=IF(MAX(INDEX(--ISNUMBER(SEARCH('LOOK UP'!A$2:$A$7,A2)),)),"MALE","FEMALE")
Last edited by leelnich; 02-09-2018 at 08:33 AM.
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
Thank you all for the responses
But the string to be searched does not appear on a defined position. Need to search it on any position with the series of strings delimited by "/"
Thank you
The formulas from posts #5 and #6 both find the look_for strings in ANY position within the look_in strings.
Hi
Im looking for the strings on LOOK UP Sheet, Column A on the RAW - BEFORE Column A.
Need to output what it is on LOOK UP Sheet, Column B on RAW - BEFORE Column B.
A friend gave me this formula
=IF(SEARCH('LOOK UP'!A:A,'RAW - BEFORE'!A:A)=1,'LOOK UP'!B:B,0)
but the 'LOOK UP'!A:A should be on the same line with 'RAW - BEFORE'!A:A to have a value
help on this please
TIA
Assuming you are going to complete the lookup list (the women are currently not accounted for), try this in B2 of the after sheet, copied down:
=IFERROR(VLOOKUP(MID(A2,4,FIND("/",A2,4)-5),'LOOK UP'!$A$2:$B$7,2,0),"")
Last edited by AliGW; 02-16-2018 at 06:16 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks