If statement

[ekhawaja]

Hello,

Looking for a formula that spits out the code as shown in col B if col A contains the specific number. Note that I have I have this data in thousands of rows. Thanks in advance.

1.1	V-1
1.2	V-1
2.1	V-3
2.2	V-3
2.3	V-5
2.4	V-4
2.5	V-4
2.6	V-3
4.0	V-7
4.1	V-9
4.2	V-9
8.0	V-8
9.1	V-9
9.2	V-9
9.3	V-9
9.4	V-9
9.5	V-9
9.6	V-9

 

[Joe4]

I think more explanation is necessary.

Are you saying that in the table above, you are trying to derive the second column from the first column?
If so, please explain the logic, because I am not seeing it.

Or, are you saying that is a "lookup" table, so whenever you look up a value like "4.2", you want it to return "V-9"?
If that is the case, you already have your lookup table above, and can just use a VLOOKUP formula.
See: VLOOKUP function - Microsoft Support

 

[CesarF]

hi @ekhawaja,
Another approach, assuming your list is static, can be done with two strings (in a macro) or cells (with formulas) with the same length:
"|1.1|1.2|2.1|...|9.6|", and "|V-1|V-1|V-3|...|V-9|"

Suppose you're using formulas and your list starts in column A and row 2.
Put the first and second strings in cells A1 (original) & B1 (result).
Now in B2, put this =mid($B$1,search($A$1, A2),3)
Then copy B2 to B3..B###