Formula with multiple answers

Aimee Colton

Hello,

Is there a formula for a row that can have multiple answers? I have one column A with names, and depending on that name in column A, I want a different number to appear in column B

For example

If column A=Mary, return 245 or column A=James, return 548…..etc.

I have 5 names with 5 different values associated with them

=IF(or(Forename1 "Mary"), RefNum1, "245", IF(or(Forename1, "James"), RefNum1, "548"), … but keep getting #unparseable

Kerry St. Thomas

From your description, this sounds like an INDEX/MATCH formula would do the trick if you have that list pre-prepared. For example, if you have one Sheet that has this:

then you can do a lookup against that sheet with this formula:

=INDEX(Return:Return, MATCH(LookupName@row, Forname:Forname, 0))

Doing this with a lookup table like this would be a bit more scalable, because then when someone is added to the list you won't have to redo a hard-coded formula.

If you're tied to using the nested IF/OR format, it looks like the syntax for those is a bit off. Check out these links for more on the individual formula syntax:

https://help.smartsheet.com/function/or

https://help.smartsheet.com/function/if

Paul Newcome

What exactly is "RefNum1"?

Aimee Colton

Hi Paul,

RefNum1 is reference number

Aimee Colton

Hi Kerry,

I tried the index match formula by referencing a separate sheet called Staff Info, which has the staff member's name and reference number, but I can't get it to work.

I tried the following:

=INDEX({StaffRefNum:StaffRefNum}, MATCH({StaffName:StaffName},0)2)

=INDEX({StaffRefNum}, MATCH({StaffName},0)2)

=INDEX(StaffRefNum:StaffRefNum, MATCH(StaffName:StaffName,0))

=INDEX({StaffRefNum}:{StaffRefNum}, MATCH({StaffName}:{StaffName},0))

all UNPARSEABLE

=INDEX({StaffRefNum}, MATCH({StaffName}, 0)) - INCORRECT ARGUMENT SET

and

=INDEX({Position Number}, MATCH("{Staff Name}", {Staff Name}, 0)) - NO MATCH

What am I doing wrong?

Thanks

Aimee

Paul Newcome

Try putting an actual name in this one:

=INDEX({Position Number}, MATCH("need something to actually match on here", {Staff Name}, 0))

Aimee Colton

Hello,

I found this - https://support.microsoft.com/en-gb/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45 , does Smartsheets support the IFS function?

If so could someone tell me where I went wrong with this formula?

=IFS([First Name]@row=Employee1,Number1,[First Name]@row=Employee2,number2,[First Name]@row=Employee3,Number3,[First Name]@row=Employee4,Number4,[First Name]@row=Employee5,Number5,"0")

heyjay

https://community.smartsheet.com/discussion/comment/444008#Comment_444008

Consider using IF AND function.

Paul Newcome

@Aimee Colton Smartsheet does not support the IFS function. You would need to use a nested IF formula.