Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Using if statement many times!
Register FAQ Mark Forums Read

Reply
 
Thread Tools Display Modes
  #1  
Old 01-18-2012, 02:22 PM
Jamal NUMAN Jamal NUMAN is offline Using if statement many times! Windows 7 64bit Using if statement many times! Office 2010 64bit
Expert
Using if statement many times! 		 
Join Date: Nov 2010
Posts: 615
Jamal NUMAN is on a distinguished road
Question Using if statement many times!
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
Reply With Quote
  #2  
Old 01-18-2012, 02:39 PM
tupham tupham is offline Using if statement many times! Windows XP Using if statement many times! Office 2007
Advanced Beginner
  
Join Date: Aug 2008
Posts: 57
tupham is on a distinguished road
Default
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.
Attached Files
File Type: xlsx Book1.xlsx (7.8 KB, 20 views)
Reply With Quote
  #3  
Old 01-18-2012, 03:08 PM
excelledsoftware excelledsoftware is offline Using if statement many times! Windows 7 64bit Using if statement many times! Office 2003
IT Specialist
  
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default
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.
Reply With Quote
  #4  
Old 01-18-2012, 10:40 PM
Jamal NUMAN Jamal NUMAN is offline Using if statement many times! Windows 7 64bit Using if statement many times! Office 2010 64bit
Expert
Using if statement many times! 		 
Join Date: Nov 2010
Posts: 615
Jamal NUMAN is on a distinguished road
Thumbs up
Quote:
Originally Posted by tupham View Post
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
Reply With Quote
  #5  
Old 01-19-2012, 03:44 AM
Catalin.B Catalin.B is offline Using if statement many times! Windows Vista Using if statement many times! Office 2010 32bit
Expert
  
Join Date: May 2011
Location: Iaşi, România
Posts: 386
Catalin.B is on a distinguished road
Default
Anyway, in a single cell you can use only 7 nested IF 's for all versions of excel.
Reply With Quote
  #6  
Old 01-19-2012, 11:57 AM
Jamal NUMAN Jamal NUMAN is offline Using if statement many times! Windows 7 64bit Using if statement many times! Office 2010 64bit
Expert
Using if statement many times! 		 
Join Date: Nov 2010
Posts: 615
Jamal NUMAN is on a distinguished road
Thumbs up
Quote:
Originally Posted by Catalin.B View Post
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
Attached Images
File Type: jpg Clip_284.jpg (78.9 KB, 11 views)
File Type: jpg Clip_285.jpg (84.4 KB, 11 views)
Reply With Quote
  #7  
Old 01-19-2012, 12:05 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Using if statement many times! Windows 7 32bit Using if statement many times! Office 2010 32bit
Expert
  
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default
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.
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Hybrid Mode Hybrid Mode


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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:49 PM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft