Need urgent help creating a nested if/lookup problem or other solution
 

Hi Guys

I have a table

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solution
 

Have a look in the help index for VLOOKUP

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gini76" wrote in message
...

Hi Guys

I have a table

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solution
 

vlookup wont work as it needs to come back with a result depending on
the name and size of carpet

there are 3 dif sizez 4 6 and 8 and need a price depending on carpet so
it wont be a simple vlookup needs to include an if function and also
needs to lookup the name of the carpet with it


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solution
 

On Sun, 8 Feb 2009 22:15:00 +0000, gini76
wrote:


Hi Guys

I have a table

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x


Try this formula in cell D32:

=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0))

Hope this helps / Lars-Åke

Need urgent help creating a nested if/lookup problem or other solution
 

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Perhaps something like this would help:
=VLOOKUP(A30,A74:D84,A31/2,TRUE)

Need urgent help creating a nested if/lookup problem or other
 

Hi,

Well normally you couldn't use a simple VLOOKUP but since your data just
happens to be in sizes 4,6,8 you can use the following

=VLOOKUP(A30,A74:D84,A31/2,)

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"gini76" wrote:


vlookup wont work as it needs to come back with a result depending on
the name and size of carpet

there are 3 dif sizez 4 6 and 8 and need a price depending on carpet so
it wont be a simple vlookup needs to include an if function and also
needs to lookup the name of the carpet with it


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solution
 

thanks to all...

Lars i used yours and it works.. ur a genious thanks there go my
sleepness nights... till the next section of the coursework

thanks a billion
xxaha!


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solu
 

Hi,

Your basic formula is

=VLOOKUP(AP4,A4:Z40,26,)

As far as making 28 copies of the sheet:
1. Press and hold the Shift+F11 key until you have 28 copies (pretty quickly)
2. Move to the sheet with the data.
3. Click the top left corner of the sheet to select the whole thing and
press Ctrl+C (copy)
4. Click the sheet tab for the first of the new sheets, hold down the Shift
key and click on the last sheet of the 28 you just added, this will select
from the active sheet to the last sheet.
5. Press Ctrl+V (paste).
6. Right click on the active sheet's tab and choose Ungroup sheets.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"gini76" wrote:


Hi Guys

I have a table

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solution
 

Lars-Åke Aspelin;219918 Wrote:
On Sun, 8 Feb 2009 22:15:00 +0000, gini76
wrote:


Hi Guys

I have a table

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x


Try this formula in cell D32:

=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0))

Hope this helps / Lars-Åke


Can i ask you to explain this formula as i like to know how things work
so can do them again :)

thanks for the help


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solution
 

You may feel better about it if you explain it to yourself by looking in the
help index for MATCH and then for INDEX.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"gini76" wrote in message
...

Lars-Åke Aspelin;219918 Wrote:
On Sun, 8 Feb 2009 22:15:00 +0000, gini76
wrote:


Hi Guys

I have a table

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x


Try this formula in cell D32:

=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0))

Hope this helps / Lars-Åke


Can i ask you to explain this formula as i like to know how things work
so can do them again :)

thanks for the help


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or other solu
 

Do believe your post was meant for the thread just below (OP = barry), not
here
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Need urgent help creating a nested if/lookup problem or other solution
 

In d31 i have an if function
IF(D24<=4,"4",IF(D24<=6,"6",IF(D24<=8,"8")))

so that returns the size of the carpet now if i use that function in
d31 and have the
=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) in cell d32 it
doesnt work

if i simply type in a 4 or 6 or 8 it does but part of my qustion is to
automaticaly have the 4 6 or 8 calculated... any ideas.





Lars-Åke Aspelin;219918 Wrote:
On Sun, 8 Feb 2009 22:15:00 +0000, gini76
wrote:


Hi Guys

I have a table

Carpet m2 4 6 8
Golden Berber Twist 22.99 26.99 28.99
Eco Blue 14.99 18.99 21.99
Royal Crown 19.99 21.99 24.99
Southern Stripes 12.99 14.99 18.99
Black Widow 18.99 20.99 22.99
Play Town 19.99 21.99 24.99
Royal Keshan 14.99 18.99 21.99
Motet Grey 14.99 18.99 22.99
weave crown 21.99 24.99 26.99
Wild Flower 19.99 21.99 23.99
Classic Floral 26.99 28.99 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x


Try this formula in cell D32:

=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0))

Hope this helps / Lars-Åke


--
gini76
------------------------------------------------------------------------
gini76's Profile: http://www.thecodecage.com/forumz/member.php?userid=118
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=60469

Need urgent help creating a nested if/lookup problem or othersolution
 

Ah, you've answered my earlier question from another thread. Your IF
function is returning text values, not real numbers. All you need in
D31 is:

=D24

Hope this helps.

Pete

On Feb 9, 4:42*pm, gini76 wrote:
In d31 i have an if function
IF(D24<=4,"4",IF(D24<=6,"6",IF(D24<=8,"8"))) * *

so that returns the size of the carpet now if i use that function in
d31 and have the
=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) in cell d32 it
doesnt work

if i simply type in a 4 or 6 or 8 it does but part of my qustion is to
automaticaly have the 4 6 or 8 calculated... any ideas.

Lars-Åke Aspelin;219918 Wrote:





On Sun, 8 Feb 2009 22:15:00 +0000, gini76
wrote:

Hi Guys

I have a table

Carpet m2 * * *4 * * * 6 * * * 8
Golden Berber Twist * *22.99 * 26.99 * 28.99
Eco Blue * * * 14.99 * 18.99 * 21.99
Royal Crown * *19.99 * 21.99 * 24.99
Southern Stripes * * * 12.99 * 14.99 * 18.99
Black Widow * *18.99 * 20.99 * 22.99
Play Town * * *19.99 * 21.99 * 24.99
Royal Keshan * 14.99 * 18.99 * 21.99
Motet Grey * * 14.99 * 18.99 * 22.99
weave crown * *21.99 * 24.99 * 26.99
Wild Flower * *19.99 * 21.99 * 23.99
Classic Floral 26.99 * 28.99 * 34.99

The table starts in A73 and goes down to A84 as i need to have it all
on the same sheet just hidden.

In cell d30 i have the name of a carpet for example Motet Grey
In cell D31 i have the size roll needed for example 4
in cell D32 i want the price of the roll to show up automatically

Any Ideas?

Thanks

Gina
x

Try this formula in cell D32:

=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0))

Hope this helps / Lars-Åke

--
gini76
------------------------------------------------------------------------
gini76's Profile:http://www.thecodecage.com/forumz/member.php?userid=118
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=60469- Hide quoted text -

- Show quoted text -