[radhaanand]

My data table has three columns

Column 1 : type of a component -name given
Column 2 :their size-(data is given in the form of a range)
Column 3 :cost/unit

Eg :
apple <4 50
apple >7 70
pear 5 100

What formula do I use to find the cost of apple the size of which is 8 ?

Regards

[etaf]

-(data is given in the form of a range)

so the entry in the field is text with a > or < sign

is this in excel or access or other program SQL ???

if a spreadsheet - can you upload an example

[radhaanand]

Thanks for your reply.
I am working in an excel spreadsheet.
Please find attached a sample sheet.
The field has a number with a conditional operator prefix.

Regards

[etaf]

so in your example
grapes 3
would not return a value
and
Apple 4 would also not return a value

correct

[radhaanand]

My apologies for not have given you the complete details

Infact the original data has values for all combinations.

Please find attached the revised sheet.
Regards

[etaf]

i'm having a little trouble on this, so rather than leave you hanging - I'm very rusty - so an excel guru please step in at any time
I have also moved to business applications - where you should get a lot more response

I can evaluate the greater than using a defined function
So

Function EVALSTR(strFormula As String)
EVALSTR = Evaluate(strFormula)
End Function

That will do a simple compare on your table- for example
=Evalstr(B10&B2)
and can be used in an IF statement

A function to lookup two values is quite easy
But its doing the evaluating for the 2nd column that I stuck at right now - probably needs to be VBa

if the table had all the values in the 2nd column rather than a >< it would be simple
2 value lookup function..
or index and match -
have a look here
http://www.contextures.com/xlFunctio...ml#IndexMatch4

i will press on,