|
|
|
date: Mon, 18 Aug 2008 19:09:01 -0700,
group: microsoft.public.excel.newusers
back
formulas to match row in a table from given values
I have a table where column A is the size of the material, e.g.
0.5",1",1.5"..., columb B is the thickness, e.g. 11,14,16, of the material,
column C is the weight of the material per size and thickness.
Each size (column A) has 3 or more thicknesses (column B). So I'm trying to
write a formula that will find the given (size and thickness) values on the
same row and return the weight from column C. I've tried
L-UP,VL-UP,HL-UP,MATCH,INDEX.
MY HEAD IS SWIMMING.
Might anyone have a suggestion. advil aint workin no more. hope this isn't TMI
Thanks for any response.
date: Mon, 18 Aug 2008 19:09:01 -0700
author: james
Re: formulas to match row in a table from given values
A multi-criteria index n match (array-entered) should work here
Assuming paired inputs made in E2:F2 down,
eg in E2: 0.5 (size), in F2: 14 (thickness)
put in G2, press CTRL+SHIFT+ENTER to confirm the formula (ie array-enter):
=INDEX($C$2:$C$100,MATCH(1,($A$2:$A$100=E2)*($B$2:$B$100=F2),0))
Copy G2 down to return the corresponding weights. Adapt the ranges to suit.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
"james" wrote:
> I have a table where column A is the size of the material, e.g.
> 0.5",1",1.5"..., columb B is the thickness, e.g. 11,14,16, of the material,
> column C is the weight of the material per size and thickness.
> Each size (column A) has 3 or more thicknesses (column B). So I'm trying to
> write a formula that will find the given (size and thickness) values on the
> same row and return the weight from column C. I've tried
> L-UP,VL-UP,HL-UP,MATCH,INDEX.
> MY HEAD IS SWIMMING.
> Might anyone have a suggestion. advil aint workin no more. hope this isn't TMI
> Thanks for any response.
date: Mon, 18 Aug 2008 19:29:00 -0700
author: Max
Re: formulas to match row in a table from given values
Max~ just one ?. When I enter the thickness ie 16 it changes to 1.6E+01.
This is not a complaint only a ?. I've studied the formula and have learned
alot
and realize I need to learn more on array entered formulas which I will as
time goes by. As for now I WILL SHOUT IT WITH MY LOUDEST FONT THANK YOU X
A GUZILLION. FOR YOUR HELP . I only hope that one day I can be of some help
to another user as the group in this community has been for me. Even those
who pose ?'s have helped THANKS TO ALL.
"Max" wrote:
> A multi-criteria index n match (array-entered) should work here
> Assuming paired inputs made in E2:F2 down,
> eg in E2: 0.5 (size), in F2: 14 (thickness)
> put in G2, press CTRL+SHIFT+ENTER to confirm the formula (ie array-enter):
> =INDEX($C$2:$C$100,MATCH(1,($A$2:$A$100=E2)*($B$2:$B$100=F2),0))
> Copy G2 down to return the corresponding weights. Adapt the ranges to suit.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:17,400 Files:358 Subscribers:55
> xdemechanik
> ---
> "james" wrote:
> > I have a table where column A is the size of the material, e.g.
> > 0.5",1",1.5"..., columb B is the thickness, e.g. 11,14,16, of the material,
> > column C is the weight of the material per size and thickness.
> > Each size (column A) has 3 or more thicknesses (column B). So I'm trying to
> > write a formula that will find the given (size and thickness) values on the
> > same row and return the weight from column C. I've tried
> > L-UP,VL-UP,HL-UP,MATCH,INDEX.
> > MY HEAD IS SWIMMING.
> > Might anyone have a suggestion. advil aint workin no more. hope this isn't TMI
> > Thanks for any response.
date: Thu, 21 Aug 2008 16:30:02 -0700
author: james
|
|
