Excel Coding Error!

[Guest]

Hello

Having a little problem getting this code to work I tired many things
including IF commands & VLOOKUP and I either get a N/A or False response from
the codes I have tried. It consists of three columns listed below.

A / B / C

Column A: had random numbers from 1 to 8
Example:
1. 4
2. 5
3. 1
4. 8

Column B: Could have numbers from 17 to 45 being listed
Example:
1. 17
2. 42
3. 25
4. 32

Column C is where I want the result to show either RFA1, RFA2 or UFA
This depends on the two factors:
Example:

If column A =1 (A3) then lookup column B (B3) =25 Then LOOKUP this chart
listed below: 25 = RFA2

17 = RFA1
18 = RFA1
19 = RFA1
20 = RFA1
21 = RFA1
22 = RFA1
23 = RFA1
24 = RFA1
25 = RFA2
26 = RFA2
27 = RFA2
28 = RFA2
29 = UFA
30 = UFA
31 = UFA
32 = UFA
33 = UFA
34 = UFA
35 = UFA
36 = UFA
37 = UFA
38 = UFA
39 = UFA
40 = UFA
41 = UFA
42 = UFA
43 = UFA
44 = UFA
45 = UFA


The chart above will be listed on a separate page if needed, but I’m
thinking a chart may not be required. Any help would be appreciated

 

[Biff]

Hi!

What's the relation of column A to column B? Are you only interested in
column A values of 1? If so:

=IF(A1=1,IF(ISNUMBER(B1),LOOKUP(B1,{0;17;25;29},{"";"RFA1";"RFA2";"UFA"}),""),"")

Biff

 

[Guest]

Actually both columns are required.

Because if column A1 =1 then it should look into column B1 and get the
information from there then look into the chart below. To give the value in
column C1


17 = RFA1
18 = RFA1
19 = RFA1
20 = RFA1
21 = RFA1
22 = RFA1
23 = RFA1
24 = RFA1
25 = RFA2
26 = RFA2
27 = RFA2
28 = RFA2
29 = UFA
30 = UFA
31 = UFA
32 = UFA
33 = UFA
34 = UFA
35 = UFA
36 = UFA
37 = UFA
38 = UFA
39 = UFA
40 = UFA
41 = UFA
42 = UFA
43 = UFA
44 = UFA
45 = UFA

Thanks for the help Biff

 

[Guest]

Ok I tried that code and it seems to work great but there’s one little
problem it only works a blank worksheet but not the sheet that I have all my
data. The data sheet it’s not listing anything leaving it blank. What could
that be?

 

[Biff]

Ok, I'm not understanding this!

How? What is the relation?

Based on you posted sample:

A...........B..........C
4...........17
5...........42
1...........25
8...........32

What results would you expect in column C?

Biff

 

[Guest]

The code you posted first works fine Biff Thanks... Just for some strange
season it's not showing anything if I add the code to a data sheet I have
created but works fine if I create a new sheet... Weird I must say.

 

[Ragdyer]

I'll bet your "numbers" are not *true* XL numbers, but perhaps numeric text.

 

[Guest]

And correcting this would require?

I'll bet your "numbers" are not *true* XL numbers, but perhaps numeric text.

 

[Gord Dibben]

Format all to General.

Copy an empty cell then select all "numbers" cells and Paste Special>Add>OK>Esc.

This will coerce the text nums to real nums.


Gord Dibben MS Excel MVP

 

[Ragdyer]

Actually, the second half of Gord's suggestion will take care of the
formatting, where you don't have to format to General in a separate, extra
step.