Is IF an efficient way to solve problem.

[btahu]

I have a column of no's inside a spreadsheet which designate a class of products within a group. I have tried to use the IF but it only works for the 1st argument and not the 2nd 3rd etc. What am I doing wrong and is there a more efficient way of doing this simple chore.
A B C
1 2012
2 2012
3 2013
4 3019
5 6014
6 7015

I am trying =IF(B1={2012,2013,3019,6014,7015},"Bats","Balls","Gloves","Wheels","Pumps")

Thanks in advance,

Bill Tahu

[Juan Pablo González]

I think you're looking for:

=VLOOKUP(B1,{2012,"Bats";2013,"Balls";3019,"Gloves";6014,"Wheels";7015,"Pumps"},2,0)

[stevebausch]

IF will return one value, or another, it is not expected to return multiple values.

Were this in VBA, I would suggest Select Case.

What you are trying to do is:

IF the value is ______, then _______
OR
IF the value is ______, then _______
OR
IF the value is ______, then _______

You get the idea.

You will need to construct a number of IF statements, and string them together.

In VBA, the best construct would be Select Case.

I stay away from complex cell formula, and this would be a complex cell formula, if it can be done at all.

I am not the person to help you on this, since my gut feeling is "it can't be done".

[btahu]

Hi Juan and thanks,

Can I use multiples
i.e Vlookup(B1, {2001,2003,3004,2012,"Bats" and what does the ,2,0, signify at the end of the Vlookup. Is there a limitation to how many records I can have in the Vlookup.

I am not a VBA exponent but is that a suitable solution and relatively simple?

Regards

Bill T

[Juan Pablo González]

Check this example.

In the first one, there's no EXACT match, that's why using FALSE (or 0) returns N/A, in the second one, there is an EXACT match.

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows (32-bit) 4.90
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
A1 =
	A	B	C	D	E
1		Column1	Column2	Column3	Column4
2		10	1	50	900
3		20	2	60	1000
4		30	3	70	1100
5		40	4	80	1200
6
7
8		Looked Value	31
9
10			TRUE	FALSE
11	Which column ?	1	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B11,C$10)')>30	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B11,D$10)')>#N/A
12		2	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B12,C$10)')>3	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B12,D$10)')>#N/A
13		3	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B13,C$10)')>70	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B13,D$10)')>#N/A
14		4	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B14,C$10)')>1100	:alert('=VLOOKUP($C$8,$B$2:$E$5,$B14,D$10)')>#N/A
15
16		Looked Value	10
17
18			TRUE	FALSE
19	Which column ?	1	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B19,C$10)')>10	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B19,D$10)')>10
20		2	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B20,C$10)')>1	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B20,D$10)')>1
21		3	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B21,C$10)')>50	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B21,D$10)')>50
22		4	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B22,C$10)')>900	:alert('=VLOOKUP($C$16,$B$2:$E$5,$B22,D$10)')>900
Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.28]
If you want FREE SOFT, click here and Colo will email the file to you
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo

[btahu]

HI Juan,

I have the following entered in C1 it finds the 2012 and correctly enters Bats, however the next line C2, which has 2013 is #N/A.

=VLOOKUP(B1,{2012,"Bats",2013,"Bats"},2,0)

What am I doing wrong? I have looked at the formating, all appears to be normal however I can not get rid of the #N/A

Regards,

Bill T

[Aladin Akyurek]

On 2002-05-22 23:06, btahu wrote:
HI Juan,

I have the following entered in C1 it finds the 2012 and correctly enters Bats, however the next line C2, which has 2013 is #N/A.

=VLOOKUP(B1,{2012,"Bats",2013,"Bats"},2,0)

What am I doing wrong? I have looked at the formating, all appears to be normal however I can not get rid of the #N/A

Regards,

Bill T

You need to use ; to separate pairs of values:

=VLOOKUP(B1,{2012,"Bats";2013,"Bats"},2,0)

[btahu]

HI All,

Thanks for the help works fine - 5500 line items, 51 categories, and 10 groups. Is a large Vlookup table, so VBA may be needed for the future?

Regards & Thanks


Bill T