How to display values based on different texts from another column

[Nellycorn]

Hello,
Can anyone please help make a formula that will display various values on a column, based on different texts from another column.
Here are the texts (quotations included) and the values to display on another column based on the texts
Text=Value
"00"=300
"0"=360
"1"=420
"2"=420
"3"=420
So far the this the only formula I figured out that managed to make this work:

But because I added + in the formula, the value for "0" gets added with the value of "00". e.g the value for "0" is 360 and the value for "00" is 300,
when I type 300 as the value for "00" in the formula, it shows in column B as 660 because the value from "0" is adding to the value of "00".
However, I just want a formula that would automatically display the right values on Column B, based on the text from Column A. Please help, I would really appreciate it.

 

[Joe4]

Why not just set-up a lookup table, and use a VLOOKUP formula?
See: How to use the Excel VLOOKUP function | Exceljet

Or you could use a formula like this:

=LOOKUP(A4,{"00","0","1","2","3"},{300,360,420,420,420})

 

[Nellycorn]

Why not just set-up a lookup table, and use a VLOOKUP formula?
See: How to use the Excel VLOOKUP function | Exceljet

Or you could use a formula like this:

=LOOKUP(A4,{"00","0","1","2","3"},{300,360,420,420,420})[/
[/QUOTE]

Hello,
Can anyone please help make a formula that will display various values on a column, based on different texts from another column.
Here are the texts (quotations included) and the values to display on another column based on the texts
Text=Value
"00"=300
"0"=360
"1"=420
"2"=420
"3"=420
So far the this the only formula I figured out that managed to make this work:
View attachment 60058
View attachment 60057
But because I added + in the formula, the value for "0" gets added with the value of "00". e.g the value for "0" is 360 and the value for "00" is 300,
when I type 300 as the value for "00" in the formula, it shows in column B as 660 because the value from "0" is adding to the value of "00".
However, I just want a formula that would automatically display the right values on Column B, based on the text from Column A. Please help, I would really appreciate it.

Also, I forgot to mention that I don't want to use a table. I have multiple tables on my file so I want to keep it as few as I can. I hope that's possible

 

[Joe4]

Also, I forgot to mention that I don't want to use a table. I have multiple tables on my file so I want to keep it as few as I can. I hope that's possible

Did you look at/try the formula I posted at the bottom of first reply that does not require a table?

 

[jtakw]

Hi,

If the values in column A actually has the quote marks around them (e.g. "00", "0"), try this:

Book3.xlsx
	A	B	C
2			300
3
4	"00"
Sheet1056

Cell Formulas
Range	Formula
C2	C2	=LOOKUP(2,1/SEARCH({"00","""0""","1","2","3"},A4),{300,360,420,420,420})

 

[Nellycorn]

Why not just set-up a lookup table, and use a VLOOKUP formula?
See: How to use the Excel VLOOKUP function | Exceljet

Or you could use a formula like this:

=LOOKUP(A4,{"00","0","1","2","3"},{300,360,420,420,420})

Hi Joe4, sorry I forgot to mention (and I don't know how to edit my post cause I'm new here) that I don't want add a table to make the formula because I already have a lot of tables in my original file so I just want a formula (if that's possible) but thank you very much, I'll use it if I really have to

 

[jtakw]

So have you tried the formulas in Post #2 by Joe4, and mine in Post #5 ???

 

[Joe4]

Hi Joe4, sorry I forgot to mention (and I don't know how to edit my post cause I'm new here) that I don't want add a table to make the formula because I already have a lot of tables in my original file so I just want a formula (if that's possible) but thank you very much, I'll use it if I really have to

You did mention it in your prior post, and I responded back already indicating that I posted a non-table formula option at the bottom of my initial reply.

 

[Nellycorn]

You did mention it in your prior post, and I responded back already indicating that I posted a non-table formula option at the bottom of my initial reply.

Oh my bad, I should have expanded on your post (sorry again, just joined this site so I wasn't smart to pick that up lol). Thank you for your consideration, I did try it out the non table formula and it ended up as N/A but jtakw's formula did work. Again, Thank you for the help, I appreciate it

 

[Nellycorn]

So have you tried the formulas in Post #2 by Joe4, and mine in Post #5 ???

Hello, yes I did try both and yours did work :D. Thank you so much, you have relieved me from the ton of further stress from out the formula myself <3