vlookup blues

Posted by Bernadette Hall on December 03, 2001 1:50 AM

hi
my spreadsheet looks up two values from different cells to return a value for one. confused? the company sells different kinds of stone, and different rates for different thickness. the lookup formula looks something like this: [thanks to the guy who helped me with this part :)]
=VLOOKUP(A2&"-"&C2,$G$2:$H$15,2)
where a2 is the name of stone eg blue, and c2 the thickness. the other part is a large table.
the problem i am having with this, is that although excel returns a value, it is often the wrong one, usually whatever is at the bottom of the table, and i have no idea why!
if u can help me with this i would be very grateful, because i am really tearing my hair out. i can send u an abbreiviated copy of the spreadsheet, so u can see for yourself.
cheers :)

Posted by Aladin Akyurek on December 03, 2001 3:07 AM

Bernadette --

As I look at the formula, I'd expect that G2:G15 consists of entries like "blue-7", blue being a name and 7 the associated thickness. If my expectation is right, then the VLOOKUP formula must do an exact match, so expand it to:

=VLOOKUP(A2&"-"&C2,$G$2:$H$15,2,0)

Still in trouble, you can send me a copy.

Aladin

========

Posted by Kurt Lyons on December 04, 2001 6:56 PM

Hi, I thought I'd add my 2cents. You may also need to do a sort on the Array. Excel has a funny way of looking at numbers. 1,10,11,12,2,20,21,22,etc is in acending order rather than 1,2,3,4 we are used to. If you dont sort the array it will confuse things as your results will vary.

Hope that helps.

Posted by Bernadette Hall on December 05, 2001 3:11 AM

Thank You very much :)

thanks for all your help with this, it now works fine...... until the next problem :)
berney