Nesting more than 7 IF statements (Excel 2003)

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

I have an inventory worksheet with many rows, where column D is a manual
entry of item length, column E is a manual entry of item width, and column G
is the weight of that size item. The weights for each inventory item are
stored in a table on a separate worksheet, with column A listing lenghts and
row 3 listing the widths. Since IF statements are limited to 7, how else can
I have column G automatically populated with the correct weight, based on
what is manually entered in columns D & E? ie: If length (col D) is 8, and
width (col E) is 2, then go look at the table and return the value at the
intersection of length 8 and width 2. I would then need to copy that formula
all the way down my inventory sheet.
 
Dee said:
I have an inventory worksheet with many rows, where column D is a manual
entry of item length, column E is a manual entry of item width, and column G
is the weight of that size item. The weights for each inventory item are
stored in a table on a separate worksheet, with column A listing lenghts and
row 3 listing the widths. Since IF statements are limited to 7, how else can
I have column G automatically populated with the correct weight, based on
what is manually entered in columns D & E? ie: If length (col D) is 8, and
width (col E) is 2, then go look at the table and return the value at the
intersection of length 8 and width 2. I would then need to copy that formula
all the way down my inventory sheet.
Click to expand...


http://www.contextures.com/xlFunctions03.html#IndexMatch2
 
Use an INDEX(table,MATCH(..row..),MATCH(..column..)) construct. Give
some further details and I could complete the formula for you.

Hope this helps.

Pete
 
Pete,
Thanks for the offer of help- I'm so frustrated with this now. I've got the
formula exactly as it should be, but my result continues to be #N/A, even
after I convert any text into numbers.

To be more specific:
My table looks like this - Column A is lengths, Row 1 is widths
A B C D
1 3 1/2 3 5/8 3 3/4
2 1.6 3.34 3.37 3.41
3 1.7 3.52 3.56 3.60
4 1.8 3.71 3.75 3.78


My inventory sheet will list the width and length as follows:

Item Width Length Weight
100abc 3 5/8 1.8 ???
should be 3.75
200def 3 3/4 1.6 ???
should be 3.41

I'm looking for a formula for the weight column on my inventory sheet that
populates from the table based on the width and length entered on the
inventory sheet.

Thanks for any help you can provide. Excel Help is really good for someone
like myself who is a beginner at this.

Dee
 
What formula do you have right now that is "exactly as it should be" but is
giving the wrong result?
 
Okay, assuming that your table is in Sheet1 laid out as shown, and
that your width is in B2 and Length in C2 of the Inventory sheet, then
put this in D2:

=INDEX(Sheet1!$B$2:$D$4,MATCH(C2,Sheet1!$A$2:$A$4,0),MATCH(B2,Sheet1!$B
$1:$D$1,0))

(All one formula - be wary of spurious line breaks that the newsreader
might introduce).

This suits the data you have shown, but if you have a larger table in
Sheet1 you will need to adjust the cell references to suit. You can
copy the formula down column D of the Inventory sheet if you need to.

Hope this helps.

Pete
 
Pete & Glenn,
Thank you both very much for your help. The formula is now working, and is
saving me a whole lot of time..!!
Dee
 
Back
Top