Formula for matching a column in excel to two other columns

In an excel sheet I have three columns.

Column A includes first names of people in a survey, Column B includes the most popular male names, column C includes the most popular female names.

I want to see whether the names in column A are male, female, or unknown (based on Column B and C)?

Also, If a name in column A is for example "James 31" and in column B "James" exists, I want the excel to count "James 31" as "male" and not unknown!

Thank you in advance!

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Hi hmoayyed,

try this in D2 and drag it down. Hope, it works.

=IF(ISTEXT(VLOOKUP(LEFT(A2,FIND(" ",A2&" ")-1)&"*",$B$2:$B$22,1,0)),"Male",IF(ISTEXT(VLOOKUP(LEFT(A2,FIND(" ",A2&" ")-1)&"*",$C$2:$C$22,1,0)),"female","Unknown"))

Try

=IF(ISTEXT(LOOKUP(15^15,SEARCH($B$2:$B$22,A2),$B$2:$B$22)),"Male",IF(ISTEXT(LOOKUP(15^15,SEARCH($C$2:$C$22,A2),$C$2:$C$22)),"Female","unknown"))

Shukla,

15^15 wat does this mean. I never used lookup formula. Need ur help to understand this. Thanks in Advance.

Originally Posted by Manikandan Arumugam

Shukla,

15^15 wat does this mean. I never used lookup formula. Need ur help to understand this. Thanks in Advance.

In my idea, biggest number came with this exponential 15^15=4.37894E+17 and Lookup function is always look nearest biggest value in array and retrieve the results.

Undrstd. Thank U Shukla.