Using if statement many times!

Jamal NUMAN · #1 01-18-2012, 02:22 PM

Using if statement many times!

I’ve two Cells, A1 and B1. I wanted value of B1 to take a value based on the value of A1 in accordance with the table below:

Item

Kg/m2

House

250

Offices

300

Garage

600

Holes

600

Stores

1,000

Commercial

500

For example, if the A1 takes the “Stores” value then B1 takes the “1000”. To accomplish this, I constructed the If statement below and worked very well:

=IF(O2=B3,C3,IF(O2=B4,C4,IF(O2=B5,C5, IF(O2=B6, C6, IF(O2=B7, C7, IF(O2=B8, C8))))))

My point is:

What if we have long table that may contain 100 rows?

Is the “If Statement” practical to perform this kind of work?

Is there other command to help this issue?

Thank you in advance,

Regards

Jamal

tupham · #2 01-18-2012, 02:39 PM

VLOOKUP is the best way to handle this. I have attached a sample worksheet.

To use VLOOKUP you need a table that contains the fixed values. This table is then referenced to generate your data in column B.

This function can be difficult to understand so it's worth spending some time reading Excel's help and playing around with the samples it provides.

excelledsoftware · #3 01-18-2012, 03:08 PM

tupham is absolutely right. The Vlookup feature is very effective for this. You only need the reference cell, a table, and the column number. its about 1 step above the IF formula for difficulty but it is one of the best.

Jamal NUMAN · #4 01-18-2012, 10:40 PM

Quote:

Originally Posted by tupham

VLOOKUP is the best way to handle this. I have attached a sample worksheet.

To use VLOOKUP you need a table that contains the fixed values. This table is then referenced to generate your data in column B.

This function can be difficult to understand so it's worth spending some time reading Excel's help and playing around with the samples it provides.

Thank you Tupham and Excelledsoftware for the answer. this is very helpful. i'll try it and let you know.

best

Jamal

Catalin.B · #5 01-19-2012, 03:44 AM

Anyway, in a single cell you can use only 7 nested IF 's for all versions of excel.

Jamal NUMAN · #6 01-19-2012, 11:57 AM

Quote:

Originally Posted by Catalin.B

Anyway, in a single cell you can use only 7 nested IF 's for all versions of excel.

Thank you all for the informative answers. it is very helpful.

fortunately, i could replace the If statement with the VLookup which is much easier. the screenshots are attached

best

Jamal

Colin Legg · #7 01-19-2012, 12:05 PM

Quote:

Originally Posted by Catalin.B

Anyway, in a single cell you can use only 7 nested IF 's for all versions of excel.

Not in all versions - the limit was increased from 7 to 64 in Excel 2007. But using a look up of some description is definitely the preferred option here.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
IF Statement and Macros	kannon	Excel Programming	2	01-06-2012 02:51 AM
What IF statement required	dr4ke	Excel	8	09-01-2011 07:41 AM
Help with IF Statement	limpbizkit	Excel	4	02-24-2011 09:16 PM
if statement	piper7971	PowerPoint	1	08-19-2010 07:10 AM
Help with IF statement!	CPelkey	Word	1	04-12-2010 09:06 AM