loop through column and assign valueClosed

Question

Hello, 

This may be a simple one, but I'm kinda in a hurry and can't make it work.  Here's my problem...

Column A              Column B               Column C (formula)
1                            4                            4
1                            5                            4
1                            6                            4  
1                            7                            4
2                                                          5
2                                                          5
3                                                          6 
3                                                          6
3                                                          6
etc

In column C, I wanna return 4 for every 1 in column A, 5 for 2 in column A, 6 for 3 in column A etc. I've tried simple Logical functions and VLOOKUP without any luck.

Can anyone give me a formula, please?!

DK

Trowa · Answer

Hi DK,

I can think of two solutions, but both have a downside:

1. IF function. Downside: maximum of 7 criterias.
Only use when the number in column A doesn't exceed the number 7.
Put this formula in C1 and drag it down:
=IF(A10=1,4,IF(A10=2,5,IF(A10=3,6,IF(A10=4,7,IF(A10=5,8,IF(A10=6,9,IF(A10=7,10,"")))))))

2.VLOOKUP function. Downside: create an extra table.
Use when column A exceeds the number 7 and there isn't any other solution available.
Use for example column G to input as much numbers as nessecary and put their counterpart in the column next to it like:
1   4
2   5
3   6
4   7
5   8
6   9
7   10
8   11
9   12
10 13
Then use the following formula in C1 and drag it down:
=VLOOKUP(A1,$G$1:$H$10,2)


Hope this helps,
Trowa

Trowa · Answer

Hi DK,

Since you're just adding 3, why don't you try: =A1+3.

Best regards,
Trowa

Loop through column and assign value

2 responses

Similar discussions

Subscribe To Our Newsletter!