CODEWITHSUNDEEP
excelexcel-formula
CorallineVerse
CorallineVerse

Reputation: 1

Excel If text then number function

I want to create an Excel function which would return results as follows: If B2 is Frontiera, then display the value from J5 in B1, if B2 is Lleida, then display the value from J6 in B1, if B2 is Girona, then display the value from J7 in B1... and so on. I looked into it but it gets me really confused. Thank you.

Upvotes: 0

Views: 434

Answers (4)

Solar Mike
Solar Mike

Reputation: 8375

You can use either vlookup or index with match as shown:

enter image description here

Cell B1 has the vlookup and cell b4 has the index with match...

Upvotes: 0

user10735198
user10735198

Reputation:

If you don't want a cross-reference table and intend to hard code the lookup values instead, put this standard formula in B1.

=iferror(index(j5:j9, match(b2, {"frontiera", "lleida", "girona", "abc", "def"}, 0)), "other")

Adjust the j5:j9 and string literal array for more or less values.

Upvotes: 0

pnuts
pnuts

Reputation: 59475

Alternative intended to be more extensible:

=CHOOSE(MATCH(B2,{"Frontiera","Lleida","Girona"},0),J6,J7,J8)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

One option would be to use a series of nested IF functions calls. Into cell B1 enter:

=IF(B2="Frontiera", J5, IF(B2="Lleida", J6, IF(B2="Girona", J7, "Other")))

I have assumed that you want to display Other in the case where there is no match. You may continue with the above approach to handle more cases.

Upvotes: 0

Related Questions