PDA

View Full Version : Reg: search option



pankaj
09-15-2008, 05:15 AM
Hi,
I have a database (values from 10 to 10M) in excel 2000. I want to create a search option such that if they enter any value which may be within or outside the database and click search. Result should be a value: that is exact match or incremented value.
If they enter 40 and the value available in database is 30.9 and 40.2,40.5,40.9 it should return 40.2. please help me out.

mikerickson
09-15-2008, 06:53 AM
If your values are in column A and they are sorted descending,
=INDEX(A:A,MATCH(40,A:A),1)
will return what you want.

pankaj
09-15-2008, 07:55 AM
Hi Mikerickson,

Thanks for your reply.
Its working fine for ascending order data but once i change into descending it is not working.
I have attached the excel sheet.
Please do the needful.

Thanks
Pankaj

pankaj
09-15-2008, 08:13 AM
or can you please tell if the exact match is not existing in the database is it possible to give all the values existing in 40/30/20/50/60series if the number entered is 40/30/20/50/60.

mikerickson
09-16-2008, 06:24 AM
Your column A is formatted as text. And you are using K and M suffixes to indicate thousands and millions. I suggest you format the data differently.

It would take VB to make Excel think that "999" < "20K"

pankaj
09-17-2008, 06:19 PM
Hi Mikerickson,
I have converted all the text format into number. Now the problem is i dont want to restrict the value only for 40. Value keeps on changing.. In the formula MATCH(E1,A:A) where i want to assign the Cell name (E1) instead of number. The result is #NA please help me.

mikerickson
09-17-2008, 08:02 PM
What is in E1?
You should look at the Help system for MATCH to see how it works and what its limitations are.