Excel: Assign value based on input

December 13, 2010 at 03:42:37
Specs: Windows XP
MACHINE Machine Delay%
SNLS 10%
DNLS 10%
2T O/L 6%
3T O/L 6%
5T O/L 7.5%
Feedo 7.5%

Now i want any logic or formula by which if i type SNLS in a cell then it automatically assign 10% to adjacent cell.
Similarly if i type feedo in a cell it automatically assign 7.5% to adjacent cell


See More: Excel: Assign value based on input

Report •


#1
December 13, 2010 at 03:54:40
try using

=IF(A1="SNLS",10%,IF(E15="Freedo",7.5%))

Where A1 here is the cell which contains SNLS, freedo, etc. If you want all the values you mentioned then use:

=IF(A1="SNLS",10%,IF(E15="Freedo",7.5%,IF(E15="DNLS",10%,IF(E15="2T O/L",6%,IF(E15="3T O/L",6%,IF(E15="5T O/L",7.5%))))))


Report •

#2
December 13, 2010 at 04:08:41
While the IF statement offered above will certainly work, another option is VLOOKUP.

Place your data in a table, e.g.

      C            D
1   SNLS          10%
2   DNLS          10%
3   42T O/L        6%
4   3T O/L         6%
5   5T O/L         7.5%
6   Feedo          7.5%

If A1 is the cell which contains SNLS, freedo, etc. B1 should contain:

=VLOOKUP(A1, $C$1:$D$6, 2, 0)

The advantage of VLOOKUP, besides being a much shorter formula, is that you can change the table if any of your data changes (e.g. the assigned percentages) and you don't have to redo your IF statements.

Review the Excel Help files for more info on VLOOKUP.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions


Ask Question