If Formula for alphabets

[Hiteshtanya]

1. I am having one column with different material names in only alphabets (No any numbers).
2. They all materials are having their values
3. How can I put "If" formula for any other files where it needed same values for same materials.

Eg.

Materials	Cross Value
Plastic	0.8​
Irons	0.37​
Silver	0.18​

 

[mart37]

I think you need VLOOKUP.

 

[immy]

If you want the values of requisite material on another sheet so use vlookup

=vlookup($A2,A1:A10,2,0)

you should write this formula in 2nd sheet where the value of such material is required
A2 should the first material already written in the 2nd sheet and A1: A10 is the range that should be selected from your given sheet i.e. sheet 1

Hope this works

 

[immy]

If you want the values of requisite material on another sheet so use vlookup

=vlookup($A2,A1:B4,2,0)

you should write this formula in 2nd sheet where the value of such material is required
A2 should the first material already written in the 2nd sheet and A1: A10 is the range that should be selected from your given sheet i.e. sheet 1

Hope this works

 

[Hiteshtanya]

I know I can take this with "Vlookup" but I want to apply "IF" formula if I can.

 

[Joe4]

I know I can take this with "Vlookup" but I want to apply "IF" formula if I can.

You will need to nest IF statements then, which can get to be long and unwieldy pretty quickly, if there are more than a few options. And there is also a limit to how many IF statements can be nested in a single formula.

With the three in your example, it would look something like:

=IF(A2="Materials",0.8,IF(A2="Irons",0.37,IF(A2="Silver",0.18,"Not found")))

You should be able to follow the pattern, and easily add more conditions for more options.

 

[Hiteshtanya]

It works.

Thank you Joe4, Immy & mart37

 

[Joe4]

You are welcome.
And welcome to the board, by the way!

As was mentioned by the other posters, VLOOKUP (or INDEX/MATCH) is usually the preferred option, as the formula is much shorter, and it is much easier to update for changes (simply add rows to your lookup table). If you set-up your VLOOKUP formula correctly, you can make changes and not have to update your formulas. Whereas, if you are using IF formulas, and something changes, you need to update every single IF formula.

Just something to keep in mind going forward...

 

[Hiteshtanya]

Noted. Thanks