Excel Question #46

[Moonliner]

Originally Posted by Ghoulish Delight

I should write a routine that increments the thread title every time Kevy posts a question.

Better declare the counter as long int just in case.....

[Kevy Baby]

I learned something new about Excel today: the maximum number of digits in a number that you can have accurately in a cell is 15. Yes, there is a reason for it (an IEEE standard) and there are ways around it (format the cell as Text or turn on "Precision as Displayed").

A coworker was entering USPS Tracking numbers that were 19 digits and the last four digits were turned into zeroes. Did a little research and this is what I learnt. For our purposes, formatting the cells as Text worked just fine. From browsing the link below, I am not sure of the overall impact of turning on "Precision as Displayed."

http://support.microsoft.com/kb/78113

[Kevy Baby]

Alright, next question on lookups...

I have fallen in love with VLookups and I use them frequently(although for some reason I have yet to need to use an HLookup function). I am such a geek.

I am sure this is another simple one, but my searches haven't provided the results I need. Basically, I need to find the value in an array when value A matches the header across the top and value B matches the list down the side.

For example, if I had three column headers; apple, banana, and orange and three row headers; Ralph, Fred, and George. For simplicity's sake, lets say the values (left to right by row) are 1-9 (apple/Ralph is 1, banana/Ralph is 2, orange/Fred is 8, etc.)

How do I look up the value based on TWO values - so that the look up knows that when value A is (for example) orange and value B is Fred, that the result is "6"?



ETA: for what I needed tonight, I was able to cheat by using a VLookup since the column header info was just the numbers 1-8 (UPS Zones). By using this formula:

=VLOOKUP(J3,'[Lookup table 121010.xlsx]Sheet4'!$A$35:$I$42,H3+1,FALSE)

In this case, you will see I used the value in H3 (Zone) and added one to it. Since the first column of the array was the value that VLookup was looking for a match on, the second column would be Zone 1, the third Zone 2 and so on. By adding 1 to the value of the zone, it made the formula look in the correct column. I just couldn't figure out how to make it look up the correct column based on a text or non-sequential number value.

[Alex]

How will the cell that triggers the formula be formatted? That is, what exactly would be your formula's input? A single cell with value apple/Ralph? One cell says apple and the next says Ralph?

Anyway, for this array (cell A1 is the blank top left)

          apple      banana      orange
Ralph     a          b           c
Fred      d          e           f
George    g          h           i

And for simplicity sake let's say that the first half of what you're looking for (e.g. George) is in cell A12 below that array and the second half (e.g. banana) is in B12.

Then in C12 this formula would return h

=INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0))

If the input were actually in one cell you'd have put a formula into where this says "B12" and "A12" to parse the input first.

[Alex]

Hey Kevy, I'm just curious. Did you get a chance to try this and were the instructions clear?

[Kevy Baby]

Originally Posted by Alex

Hey Kevy, I'm just curious. Did you get a chance to try this and were the instructions clear?

I didn't, but I have now

I did find a minor error in your formula: you had switched the A12 and B12 references in the Match portion of the formula

=INDEX(A1:D4,MATCH(B12,A1:A4,0),MATCH(A12,A1:D1,0) ) <-- you had
=INDEX(A1:D4,MATCH(A12,A1:A4,0),MATCH(B12,A1:D1,0)) <-- this worked

When I applied it to my own needs, I ran into trouble because I was swapping the two matches (basically, trying to make the first match on the horizontal axis and the second on the vertical). Once I found my error, it worked - thank you.

Now to move it past the testing phase and put it into real world!

[Alex]

Sorry about that, glad you found the issue. I probably had the right formula (since I pasted from Excel) but described it wrong.

[Kevy Baby]

Here's another lookup challenge that I am not sure how to handle.

I get USPS and UPS Zone Charts in a format similar to this:

Zip Code    Zone
  005-212     8
  214-268     8
  270-324     8
  325         7
  326-342     8
  344         8
  346-347     8
  349         8
  350-352     7
  354-359     7
  360-363     8

If some of the Three Digit Zips are shown in a range, is there a way to have Excel look in the range? How would I tell Excel that the Zone for 289 is 8?

[Ghoulish Delight]

My first notion is this (not an easy 1-cell solution unfortunately). The following code will return the two end points of the range (assuming the range is in cell A1:

Left(A1,FIND("-",A1)-1)
Right(A1,Find("-",A1)-1)

Those are text results, and the only way I've found so far to get a numeric value is to put those formulas in ohter cells (we'll use A2 and A3), then use =Value()

So then, for a value of 289 in Cell B1 you can do a =IF(VALUE(A2)<=B2,IF(B2<=VALUE(A3),1,0),0)

You'll get a 1 if it is in the range, 0 if not.

[Kevy Baby]

I came here to ask a question about merging multiple Excel spreadsheets, but I did a little more digging and found a plug in which does it AND learned all about the DATEVALUE function.

I love learning new things in Excel