How can i create an index/ match formale for spreadsheet or a lookup
 

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 but this has been
worked out using an if function.
in cell D32 i want the price of the roll to show up automatically


ive been using
=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) but this only
works if i type in the size roll not if i have an if function in D31 !!!
any ideas

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

How can i create an index/ match formale for spreadsheet or alookup
 

Try the following:

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

Also, what does your IF formula in D31 look like? Does it return a
whole number, or could it be a fractional value that just displays as
a whole number through formatting? Does it return a text value?

Hope this helps.

Pete

On Feb 9, 5:00*pm, 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 but this has been
worked out using an if function.
in cell D32 i want the price of the roll to show up automatically

ive been using
=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) but this only
works if i type in the size roll not if i have an if function in D31 !!!
any ideas

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

How can i create an index/ match formale for spreadsheet or a lookup
 

Show us your IF formula in cell D31.

--
Biff
Microsoft Excel MVP


"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 but this has been
worked out using an if function.
in cell D32 i want the price of the roll to show up automatically


ive been using
=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) but this only
works if i type in the size roll not if i have an if function in D31 !!!
any ideas

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

How can i create an index/ match formale for spreadsheet or a look
 

How sure are you that formula is returning exactly 4 and not, say, 4.1? Try
copying and pasting special on D31 to see what value pops up in the formula
bar.

"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 but this has been
worked out using an if function.
in cell D32 i want the price of the roll to show up automatically


ive been using
=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) but this only
works if i type in the size roll not if i have an if function in D31 !!!
any ideas

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

How can i create an index/ match formale for spreadsheet or alookup
 

I've just seen your response in another thread and it answers my query
- you are returning a text value "4" rather than just 4. Actually, you
only need this in D31:

=D24

but you could amend the larger formula to this:

=INDEX(B73:D84,MATCH(D30,A73:A84,0),MATCH(D31*1,B7 3:D73))

if it is important for you to have text values in D31.

Hope this helps.

Pete

On Feb 9, 5:22*pm, Pete_UK wrote:
Try the following:

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

Also, what does your IF formula in D31 look like? Does it return a
whole number, or could it be a fractional value that just displays as
a whole number through formatting? Does it return a text value?

Hope this helps.

Pete

On Feb 9, 5:00*pm, 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 but this has been
worked out using an if function.
in cell D32 i want the price of the roll to show up automatically

ive been using
=INDEX(A73:D84,MATCH(D30,A73:A84,0),MATCH(D31,A73: D73,0)) but this only
works if i type in the size roll not if i have an if function in D31 !!!
any ideas

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...d.php?t=60932- Hide quoted text -

- Show quoted text -

How can i create an index/ match formale for spreadsheet or a lookup
 

guys i removed the " " from my if function and now it seems to work.
Will need to test tomorrow but hopefully it works!!

thanks :)


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

How can i create an index/ match formale for spreadsheet or alookup
 

Well, thanks for feeding back, but you don't really need the IF
function as I stated before.

Pete

On Feb 9, 10:29*pm, gini76 wrote:
guys i removed the " " from my if function and now it seems to work.
Will need to test tomorrow but hopefully it works!!

thanks :)

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

How can i create an index/ match formale for spreadsheet or a lookup
 

Hi I do need the if function becuase = if the longest length of the
carpet is bigger than or = x i use the 4m carpet if longest length
bigger than or = x i use 6 and so forth so i can only use an if
function.

thanks guys anyway youve been a great help thanks for all the
feed:laugh back
x


Pete_UK;222225 Wrote:
Well, thanks for feeding back, but you don't really need the IF
function as I stated before.

Pete

On Feb 9, 10:29 pm, gini76 wrote:
guys i removed the " " from my if function and now it seems to work.
Will need to test tomorrow but hopefully it works!!

thanks :)

--
gini76

------------------------------------------------------------------------
gini76's Profile:'The Code Cage Forums - View Profile: gini76'
(http://www.thecodecage.com/forumz/member.php?userid=118)
View this thread:'How can i create an index/ match formale for
spreadsheet or a lookup - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=60932)


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

How can i create an index/ match formale for spreadsheet or alookup
 

Sorry, I mis-read the IF function you posted before. Glad to hear that
you have it working now.

Pete

On Feb 10, 9:00*am, gini76 wrote:
Hi I do need the if function becuase = if the longest length of the
carpet is bigger than or = x i use the 4m carpet if longest length
bigger than or = x i use 6 and so forth so i can only use an if
function.

thanks guys anyway youve been a great help thanks for all the
feed:laugh back
x