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

[gini76]

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 al
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

 

[Don Guillett]

Have a look in the help index for VLOOKUP

 

[gini76]

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

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

 

[Lars-Åke Aspelin]

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(A7384,MATCH(D30,A73:A84,0),MATCH(D31,A7373,0))

Hope this helps / Lars-Åke

 

[MyVeryOwnSelf]

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,A7484,A31/2,TRUE)

 

[Shane Devenshire]

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,A7484,A31/2,)

 

[gini76]

thanks to all...

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

thanks a billion
xxaha

 

[Shane Devenshire]

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.

 

[gini76]

Try this formula in cell D32:

=INDEX(A7384,MATCH(D30,A73:A84,0),MATCH(D31,A7373,0))

Hope this helps / Lars-Åke

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

thanks for the hel

 

[Don Guillett]

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

 

[Max]

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
---

 

[gini76]

In d31 i have an if functio
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 i
d31 and have th
=INDEX(A7384,MATCH(D30,A73:A84,0),MATCH(D31,A7373,0)) in cell d32 i
doesnt work

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

 

[Pete_UK]

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