Share via

EXCEL Function/Formula assistance - Returning multiple values based on text strings.

Anonymous
2023-06-18T14:43:17+00:00

Hi all,

I'm trying to create a function for column B so that I can categorise my data.

So I want excel to recognise recognise keywords and return a value. However I want multiple values.

I'd like the following:

"CAR" or "BIKE" or "TRAIN" - I would like to return "DAVID" in column B

"SCOOTER" or "TAXI" or "UBER" or "TRAM" I would like to return "PETER" in column B

"WALK" or "RUN" or "JOG" I would like to return "JOHN" in column B

Thank you in advance,

Jacob

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-06-23T06:44:47+00:00

    Have you tried it? Feel free to share any update.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-06-19T01:26:10+00:00

    =IF(OR(A2="Car",A2="Bike",A2="Train"),"David",IF(OR(A2="Scooter",A2="Taxi",A2="Uber",A2="TRAM"),"Peter",IF(OR(A2="Walk",A2="Run",A2="Jog"),"John","")))

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2023-06-18T16:06:24+00:00

    I'd create a lookup range. In the screenshot below, it is in H2:I11, but it can be anywhere, even on another sheet.

    With a keyword in A2, enter the following formula in B2:

    =IFERROR(VLOOKUP(A2, $H$2:$I$11, 2, FALSE), "")

    or

    =XLOOKUP(A2, $H$2:$H$11, $I$2:$I$11, "")

    Fill down.

    Was this answer helpful?

    0 comments