Excel query

Factual Questions
1

Is there a way to enter data in a cell, say the number 20 in cell A1, and have cell A2 pop up with ‘ABC company’ - the company assigned to the number 20?
Ideally, I’m looking to have a dozen companies with multiple numbers. For example, ABC company has the number 20, 21 and 22. XYZ company has 23 and 24.
I’ve searched Excel help to no avail, and my excel-savvy family members had no idea and didn’t think it could be done. I figure I’ll give it a shot here before giving up.

As a bonus, is it possible to, when ABC company pops up, to have A3 give me a range of certain numbers, like 12345 and 6789 - these being assigned solely to ABC company?

If Excel cannot accomplish this, would someone be able to point me in the direction of some free software that can?

Thanks!

2

I’ve never used it in Excel, but I’ve used it a lot in Access. The function you want is called “LOOKUP”. Look for it in Help, and I hope you can make sense of it. Good luck!

3

Unfortunately, I don’t have Access at work. I’ll have to try it at home.

4

Lookup will do this:

Col A Col B
1 10 ABC
2 20 DEF
3 30 ABC
4 40 XYZ

=Lookup(20,a1:a4,b1:b4) will return DEF
=Lookup(10,a1:a4,b1:b4) or =Lookup(30,a1:a4,b1:b4) will return ABC

note
=Lookup(25,a1:a4,b1:b4) will also return DEF as it looks for the next lower one if no match is found

=Lookup(5,a1:a4,b1:b4) will return an error

5

Yep, the function you want is called VLOOKUP, and it works like this:

In cell A1, you put 20, as you say, and then in cell A2 put the following formula: VLOOKUP(A1,‘CompanyLookup’!$A$1:$B$100,2,FALSE). This is telling Excel to look in cell A1 for your reference (20, in this case), which looks up against the array of lookup values – in this case, a worksheet you’ll create called CompanyLookup, which in the formula given above has the data in columns A and B, lines 1 through 100. The “2” in the formula tells Excel to return a value from the second column of the array. (If your actual array is different, make the formula match what you have in terms of columns and rows.)

So set up a worksheet called CompanyLookup, and in the first column you’ll put the reference number, and in the second column, the name of the company. So you’d put 20 in the first column, and “ABC Company” in the second column, and fill up the sheet with as many companies as you have.

6

Following Keeve’s (and OldGuy’s) advice, here is a decent article describing the use of the lookup function (vector form).

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=446
LilShieste

7

[QUOTE=Lionne]
Unfortunately, I don’t have Access at work. I’ll have to try it at home.
[/QUOTE]
Sorry, I wasn’t clear. LOOKUP is in Excel too!

8

One warning, however, vlookup works well, but it can be resource hog if you fill a spreadsheet with thousands of versions of it.

9

[QUOTE=Lionne]
As a bonus, is it possible to, when ABC company pops up, to have A3 give me a range of certain numbers, like 12345 and 6789 - these being assigned solely to ABC company?

If Excel cannot accomplish this, would someone be able to point me in the direction of some free software that can?
[/QUOTE]
VLOOKUP is indeed the way to go, but as far as the bonus, I think getting Excel to jump through that hoop would be mighty tricky, if it’s even possible. You might be able to get it by playing with “array formulas” (see Help function), but you have all kinds of problems like what if a number is accidentally assigned more more than one Company? More likely you would need VBA coding for it. VBA is bundled with Excel for free but it’s a programming language so if you’re not a coder this is a big hump to get over.

When I get a really tough Excel problem (particularly a VBA problem) I use the help forum at www.ozgrid.com. It’s free but registration is required.

10

Wow, thanks everyone. I will start working on this and let you know the results.

11

It worked! Thanks largely to **LilShieste’s ** article.

I tried **OldGuy’s ** method first - it worked for my test, but when I entered all 39 numbers, it stopped working. Couldn’t for the life of me figure out why.
**Sal’s ** method was next, but I couldn’t correlate his figures to what I had. You explained it well enough, but it didn’t click in my head.

I showed my boss and he’s happy. I’m thrilled, as it will save me a lot of unnecessary writing.

Thanks!

12

[QUOTE=CookingWithGas]
When I get a really tough Excel problem (particularly a VBA problem) I use the help forum at www.ozgrid.com. It’s free but registration is required.
[/QUOTE]
I’ll keep this in mind, thanks.

13

[QUOTE=Lionne]
**Sal’s ** method was next, but I couldn’t correlate his figures to what I had. You explained it well enough, but it didn’t click in my head.
[/QUOTE]

Well, let me know if I can explain it better. VLOOKUP is a terrifically useful function, once you get the hang of it, and we use it constantly here in our work.

14

What the website explained was what each component (lookup_value, table_array, etc.) of the formula was. Which you did too, but the site was more detailed and took me to the actual vlookup function in Excel.
I understand what you posted now, but the ! and $ I don’t get. My formula looks like this:
=VLOOKUP(B13,Companies,2,FALSE)
A question…would your formula have worked if I had not selected the range in the second spreadsheet (Companies)? The site also showed me how to select a range.

15

[QUOTE=Sal Ammoniac]
Yep, the function you want is called VLOOKUP, and it works like this:

In cell A1, you put 20, as you say, and then in cell A2 put the following formula: VLOOKUP(A1,‘CompanyLookup’!$A$1:$B$100,2,FALSE).
[/QUOTE]

To piggyback on Sal’s excellent suggestion here, you could also replace the cell references ($A$1:$B$100) above with column references, like so: $A:$B. This results in Excel looking at the entire column instead of just the 100-row range it was limited to before, and means that later on down the road you don’t have to go back and figure out why it suddenly stopped working (when you get past the end of the original range, it won’t find anything any more).

Also, by playing with the other lookup functions in Excel, and the order your CompanyLookup sheet is sorted in, you can achieve a portion of the range lookup you were looking for earlier. You’d do this in another cell, of course – A3, I think you said – but the formula(s) are similar.

For example, IF you sort your CompanyLookup sheet so that all numbers are in numeric order, and IF each company has a continuous range assigned to it, then the following formula in cell A3 would give you a range result like you describe:

=INDEX(CompanyLookup!$A:$A,MATCH($A$2,CompanyLookup!$B:$B,0)) & IF(COUNTIF(CompanyLookup!$B:$B, $A$2)>1, " - " & INDEX(CompanyLookup!$A:$A, MATCH($A$2, CompanyLookup!$B:$B,0)+COUNTIF(CompanyLookup!$B:$B, $A$2)-1), “”)

On my test sheet I’ve set up ‘ABC Co’ to have numbers from 20 - 22, and the result I get in cell A3 using this formula is “20 - 22”.

What the function I’ve described is doing is straightforward, it just appears complex. It relies on you having the CompanyLookup sheet sorted correctly, so if that can’t be done then feel free to ignore this. There ARE other ways to do what you want without resorting to VB code, but it gets much more complex and may be impractical for your situation.

First, it’s using the INDEX() function coupled with the MATCH() function to perform something very similar to a VLOOKUP. VLOOKUP only works in one direction, left to right, so if you want to find something in a column to the right and return a value to the left, you have to use this method instead. So this is all it does to return the first match found for the company name.

Next, it counts how many times the company name appears in the CompanyLookup tab. If this number is more than 1, then it knows that it needs to display a range rather than a single value. Using the same INDEX() and MATCH() method as before, it (1) finds the position of the first entry again, (2) adds the number of entries for that company to that position, (3) removes 1 from that number so it won’t go past the end of the range, and finally (4) returns the value from that position in column A.

Whew!

Again, if you can’t sort your company data like I’ve described, this method won’t work properly. There ARE other ways to achieve what you’ve described, but it can get pretty messy, formula-wise. If you need to do it, though, just speak up. I’d be happy to help.

16

[QUOTE=Lionne]
What the website explained was what each component (lookup_value, table_array, etc.) of the formula was. Which you did too, but the site was more detailed and took me to the actual vlookup function in Excel.
I understand what you posted now, but the ! and $ I don’t get. My formula looks like this:
=VLOOKUP(B13,Companies,2,FALSE)
A question…would your formula have worked if I had not selected the range in the second spreadsheet (Companies)? The site also showed me how to select a range.
[/QUOTE]

You need to supply a range to the formula. Excel supports what’s called Named Ranges, which is nothing more than an ordinary range (Companies!A2:B100, for example) that has had a name assigned to it. You can refer to the name (Companies) and Excel will translate that to the range internally. It looks like you’ve got a named range set up, if the formula works and yet appears as it does above.

The ! and $ are operators – that is, special characters that trigger certain behavior in Excel. The ! is the tab identifier; it tells Excel that whatever appears to the left is the name of the tab to look at, and whatever appears to the right is where on that tab to look.

The $ is what I call the ‘lock’ operator (I can’t remember the real name, sorry). What it does is a bit harder to explain, but once you get it, you’ll love it.

Short explanation: the $ prevents Excel from modifying your formula (and thus changing what cell you’re ‘pointing’ at) when you copy/paste or extend a formula into other cells.

Long explanation: a demonstration is in order! Open up a blank sheet and put the following formula in A1: =C1

Then put the same formula in B1, but this time, put a $ in front of the 1, like so: =C$1

Finally, in cell C1, put a value. Something, anything. Your name, for example.

Now, select A1, copy it (right-click, Copy), and paste it in A2. What appears in A2 should be 0 or blank (depending on how you have Excel set up). If you look at the formula in A2, you’ll see that it has changed to “=C2”.

Do the same copy paste routine with B1 (into B2), and you’ll see the difference in behavior.

17

Sofaspud, thanks. I understand your last post, and the ! and $ now. Since I didn’t know how to do that, for my sheet, I just typed the formula in one cell and dragged it down the rows, which is why each one is different.

However, about your previous post…if I understand it right, I can set up my ‘companies’ sheet to have one row with 20-22 for Company A rather than 3 rows for Company A?
That will reduce my rows from 39 to 11. But, will I still be able to enter 21 in my blank sheet and Company A will pop up? Cause that’s my goal.
And will it be a problem if I have company A with 2-5 and company B with 8-12, with no 6 or 7? You stressed companies having numeric order…

18

OK, I think I can answer my own questions. Never mind, then.

I really should take a class. This wasn’t covered in Basic Excel!

19

[QUOTE=Lionne]
Sofaspud, thanks. I understand your last post, and the ! and $ now. Since I didn’t know how to do that, for my sheet, I just typed the formula in one cell and dragged it down the rows, which is why each one is different.

However, about your previous post…if I understand it right, I can set up my ‘companies’ sheet to have one row with 20-22 for Company A rather than 3 rows for Company A?
That will reduce my rows from 39 to 11. But, will I still be able to enter 21 in my blank sheet and Company A will pop up? Cause that’s my goal.
And will it be a problem if I have company A with 2-5 and company B with 8-12, with no 6 or 7? You stressed companies having numeric order..
[/QUOTE]

Ah, I believe I misunderstood what you were aiming for. I thought you were looking for a way to have the main sheet display the range associated with Company A. In that case, Company A would still need to have 1 row per number assigned to it in the Companies sheet.

Instead, it sounds like your ideal solution would have, say, “20-23” next to “Company A” on your company sheet, and on your main sheet you could enter 22 and still have Company A pop up. Correct?

If so, there IS a way, and it’s not hard at all. The easiest solution would involve setting up a formula on your Companies tab, to create an index of sorts.

In that case, column B would contain the list of numbers assigned to each company, separated by a comma, semicolon, or whatever (anything except a space or math operator, as those can be misconstrued by Excel). All the numbers would be in this cell. With this solution, you must explicitly include each number assigned to the company; putting “20-23” in there won’t work, but “20;21;22;23” would.

(If you REALLY REALLY need to be able to use an implicit range like “20-23”, then again, there is a way, but it can get VERY ugly to maintain. I think this would work better.)

Column C would hold the name of the company.

Then, in column A, we’d use the following formula to ‘select’ the appropriate row:

=NOT(ISERROR(SEARCH(Main!$A$1, B2)))

(Replace “Main” with the name of the main tab you’re typing the number to be located, and A1 with the correct cell if that’s not already it)

What this does is, it places a TRUE next to whichever company ‘owns’ the number you typed in. Then you can use the VLOOKUP() function to select the company name, just like before. It would change a bit, but not much – you’d just need to change the first parameter to TRUE rather than to the cell you typed a value into.

Make sense?

20

[QUOTE=Sofaspud]
Instead, it sounds like your ideal solution would have, say, “20-23” next to “Company A” on your company sheet, and on your main sheet you could enter 22 and still have Company A pop up. Correct?
[/QUOTE]
Yes, exactly. Perhaps I should have just explained what I’m doing in the first place! I work at a bank. We get bags from different companies with deposits that must be logged. With 12 companies having anywhere from 2 to 6 bags, writing down the names and account numbers every day is a huge hassle. I wanted a sheet that I could add to every day, type in the bag number, have the company name and account number pop up, and I enter the amount.
Thanks to this thread, I now have a spreadsheet with one sheet listing bag numbers and corresponding companies and their accounts. Second sheet has the formula prefilled, and when that page is full, I can print it out and file away. I can also save that sheet and create a copy of the original template to start fresh.

Your knowledge has been a big help, and I intend to play around with your newest formula tomorrow. I’ve had enough Excel today! :slight_smile: