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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sounds like you need a VLOOKUP perhaps?

It depends on how your data is formatted etc?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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"
 
Upvote 0
Do you mean somethng like.....?

=IF(AND(A2>=100000,A2<=699999),"COMPANYNAME","")
 
Upvote 0
This seems to work - once the number is established, how do I get specific text to appear in another cell?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top