Populating values in Excel based on another column of values

Question

I'm not the best with technical descriptions but please bear with me.

The task before me:

A large spreadsheet with a column of values that need to be reviewed and a different number input beside them. Unfortunately the number is not simply "value, less 15% though it's close". I will need to have a list of specific "find/replace" commands for my formula.

Example:

3.02

6.65

1.54

3.02

And I need to format it such that it says:

3.02 2.80

6.65 5.60

1.54 1.40

3.02 2.80

My idea was something along the lines of =if(A1=3.02,2.80,=if(A1=6.65,5.60,=if(A1....

Then I'd be able to just paste this formula and drag down the entire spreadsheet.

Unfortunately that didn't work and so I come to you all for help.

Please save me tons of time and figure out how I can make this spreadsheet generate it's own values!

Thanks,

Mike

Answer 1

I would make a little lookup table of the specific replacements:

      A     B
1  lookup result
2   3.02   2.8
3   6.65   5.6
4   1.54   1.4
5   3.02   2.8

And then you could set up a formula like this:

9  value   result
10  3.02    =VLOOKUP(A10,A$2:B$5,2,FALSE)
11  6.65
12  1.54
13  3.02
14  1.54

And you can drag that formula down the rest of the table.

Beware of the A$2:B$5 - if your lookup table is differently-sized, it will need to change.

OR, to keep more along the lines of what you have, try a formula like this:

=if(A1=3.02,2.80,if(A1=6.65,5.60,if(A1....

The change I made was to remove the extra = before the inner IF function calls.

Answer 2

It is hard to guess how many different value do you have. Based on your given sample data you can use XLOOKUP() function like below if you have Excel-365.

=XLOOKUP(A1,{5.6,2.8,1.4},{5.6,2.8,1.4},"",-1)