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

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

 

[Pete_UK]

Try the following:

=INDEX(B7384,MATCH(D30,A73:A84,0),MATCH(D31,B7373))

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

 

[T. Valko]

Show us your IF formula in cell D31.

 

[Sean Timmons]

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.

 

[Pete_UK]

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(B7384,MATCH(D30,A73:A84,0),MATCH(D31*1,B7373))

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

Hope this helps.

Pete

 

[gini76]

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

thanks

 

[Pete_UK]

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

Pete

 

[gini76]

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

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

 

[Pete_UK]

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

Pete