Multiple lookup criteria (vlookup?)

remarkl,

Sorry for not being as clear as I should have with respect to my problem #1.  No, I don't want to line-up the two databases one to one, because some calls might not exist at all in database 2 that exist in database 1, so that wouldn't work.  This is because database 1 has everything and database 2 does not.  

Please allow me to ellaborate a bit:

Database 2 is our long distance billing from our phone company that contains all long distance calls made from all of our worldwide offices.  Database 1 comes from our internal long-distance telephone collection software from our phone switch, in other words it's a a telephone accounting system.  This accounting system is pre-programmed to not include, or not account for, certain calls with certain criteria for specific reasons.  What I wanted to do is see is if our telephone accounting system is putting a realistic dollar amount to calls versus what we are actually being charged from the phone company.

With respect to my problem #2, I get the "duh" award on that one and am sorry to waste your time on that one.  

You may want to re-ask your first question, as EE has put this thread in the Previously Asked (and answered) category, where it will not get much attention.

The first question is fairly complex.  Rather than try to produce an entire solution, which seems to me a commercial exercise, I'd offer these hints:

1.  Create an index for each call in each database.  The index should concatenate the phone #, the date, and the time.  (I don't think the duration will actully help much.)  XL will convert a date and time into a serial integer with a decimal for the time.  (Enter =now() in cell, set the format to "number," and expand the number of decimal places shown to get an idea of what a serial date/time looks like.)  The concatenated index could be the 10-digit phone # mulitplied by 100000, plus the serial date, e.g., 555555555539756.123.  You would then test the index for each call in the company list against all of the indices in the phone company's list.  The test is tricky because...

2.  XL's vlookup can find the highest index that is less than a target index, but it cannot find the nearest value if that value is slightly above your target.  To do that, you need (I think) an array formula.  For example, the following formula will find the value in cells a1:A3 that is nearest the amount in cell B1:

=INDEX(A1:A3,MATCH(MIN(ABS(B1-A1:A3)),ABS(B1-A1:A3),0))

This formula must be entered as an array, which means you need to hit ctrl-shift-Enter to enter it instead of just Enter.  If you do it correctly, the formula will appear in brackets.  Array formulas are very cool, but they can take some getting used to.  Expanding from the inside out, wihch is how I created it, my formula says to XL:

1.  ABS(B1-A1:A3)

Create a virtual array that consists of the absolute values of the differences between the value in cell B1 and  in cells A1:A3.  For example, if the value in A1:A3 were 2, 3, and 4, and the value in B1 was 3, the virtual array would hold 1, 0, and 1, the absolute values of 2-3, 3-3, and 4-3.

2.  MIN(ABS(B1-A1:A3))

Determine the smallest value in the absolute value array.  In my example, that would be the 0 in position 2. At this point, the fomula can tell us  that the minimum difference is 0, but not which entry in the array contains it.

3.  MATCH(MIN(ABS(B1-A1:A3)),ABS(B1-A1:A3),0)

Determine which entry in the virtual array contains that minimum value.  In my example, it lies in the second position, the one that contains the absolute difference between A2 and B1, which is 0, so the MATCH function returns 2.

=INDEX(A1:A3,MATCH(MIN(ABS(B1-A1:A3)),ABS(B1-A1:A3),0))

Find the entry in A1:A3 that corresponds to the position of the minimum absolute value in the absolute value array.  The second entry in cells A1:A3 is the 3 in cell A2, so the formula returns 3, which is the closest match to the value in Cell B1, which is also 3.  

You can see that the smae result would apply if the value in B1 were 2.9.  The virtual array would hold .9, .1, and 1.1.  Thhe minimum value would be .1, which would occupy the second position in the array and return 3 as the nearest value.  Note that the reference to A1:A3 that is the first argument in the Index function is not related to the lookup process.  If the phone company call indices are in column A, and the data you want to retrieve, e.g., the dollar amounts charged by the phone company, are in column D, then

=INDEX($D$1:$D$3,MATCH(MIN(ABS(B1-$A$1:$A$3)),ABS(B1-$A$1:$A$3),0))

would return the dollar amount for the call with the index nearest the index in B1.  (I understand that the value to be looked up would not be in cell B1 but would but would be in the oher database, but the formula would then be too complicated to show here.)  Note that I switched to absolute references because that's how the formula would be used so that you could copy it down alongside each phone call in the company system list.  Actually, you would clarify things by naming the relevant ranges so that the formula looks like:

=INDEX(PhoneCoCharge,MATCH(MIN(ABS(B1-PhoneCoIndex)),ABS(B1-PhoneCoIndex),0))

where B1 is the Company system index for a particular call.  Naming the range that contains the thing you are looking up will confuse XL; it's best to stick with cell references for that purpose.

If you want to give this a try, I suggest that you experiment with a simpler set of data as I did, perhaps using the same cells and values to make sure you can make it work.   And, as I said, if you want additional ideas, you should consider reposting the question.  If you do, you may want to copy this post into the thread so that others can see it.

Good Luck

Larry Kramer