Excel variables help (Searching for specific numbers)

jamiegreen

New Member
Joined
Sep 3, 2007
Messages
9
I am trying to create an Excel database where I input a number and it finds the information that I am looking for.

For example, I have a list of 20 different companies that each have various supply numbers for properties ranging over a period of around 400,000 numbers spread out over between them. I want to be able to input a specific supply number on the database and it provides me with the relevant area, contact number for the company, and address etc.

So if the number that I input falls between 3000 and 4000, the details for one company appear. However if I input another number between 4000 and 5000 then a different company name appears, and so on.......

I am struggling with the formula, is this possible?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,166
Sounds like you need a VLOOKUP perhaps?

It depends on how your data is formatted etc?
 

jamiegreen

New Member
Joined
Sep 3, 2007
Messages
9
Thanks, so how would the Vlookup formula look?

Am I right in saying that all the relevant data needs to be on a seperate table?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Maybe this example will help

Sheet4

<table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:25px;" /><col style="width:64px;" /><col style="width:19px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Code</td><td >Desc</td><td >Price</td><td > </td><td >Washers</td><td > </td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1</td><td >Sprockets</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">2</td><td >Gromets</td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">3</td><td >Washers</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4</td><td >Taps</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">56</td><td >Traps</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td><td > </td></tr></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td >Spreadsheet Formulas</td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E1</td><td >=VLOOKUP(3,A1:C6,2,FALSE)</td></tr><tr><td >G1</td><td >=VLOOKUP("Washers",B1:C6,2,FALSE)</td></tr></table></td></tr></table>
Excel tables to the web - Excel Jeanie Html 4
 

jamiegreen

New Member
Joined
Sep 3, 2007
Messages
9
Is there any other way that I can do this, as the total of supply numbers is around 2000000000?? I want to do something for my job, where excel is used like a search engine if possible.

I need something like this:

If A2=between 100000 and 699999 then "Company name"

If A2=700000 and 999999 then "Another company name"
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,166

ADVERTISEMENT

Do you mean somethng like.....?

=IF(AND(A2>=100000,A2<=699999),"COMPANYNAME","")
 

jamiegreen

New Member
Joined
Sep 3, 2007
Messages
9
This seems to work - once the number is established, how do I get specific text to appear in another cell?
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,166
Can you explain?

You want to type a number in to A2....?
Which cell will the company name appear in.
the formula:

=IF(AND(A2>=100000,A2<=699999),"COMPANYNAME","")

only covers 1 condition.

If you have more than 7 conditions you might need a vbe solution,

something like:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("a2").Value = "" Then Range("C1").ClearContents
If Range("A2").Value >= 100000 And Range("a2").Value <= 699999 Then Range("C1").Value = "COMPANY 1"
If Range("A2").Value >= 700000 And Range("a2").Value <= 999999 Then Range("C1").Value = "COMPANY 2"
Application.EnableEvents = True
End Sub

Paste this into the sheet module and expand to suot your needs.

If the cell is blank C1 is empty
If A2 is 100000 to 699999 it shows company 1.
You can add as many conditions as needed..?

PS

I am no VB expert there might be a better way but if this is what your looking for someone else will probably come up with something better.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,388
Messages
5,595,889
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top