# VLOOKUP & MULTIPLE COLUMNS

Posted by J Davis on February 06, 2001 10:48 AM

I need to look up match data in two columns w/ vlookup
ACCOUNT CENTER AMOUNT
22222 1 \$25
22222 2 \$77
22222 3 \$16

44444 1 \$18
44444 2 \$100
44444 3 \$17

77777 1 \$16
77777 2 \$80
77777 3 \$42

CELL A1 CONTAINS 44444 AND CELL A2 CONTAINS 2
I.E. HOW DO I VLOOKUP (OR MATCH, INDEX) TO GET THE
\$100 IN ACCOUNT 44444 AND COST CENTER 2?

I WOULD MUCH APPRECIATE THE HELP

J Davis

Posted by Dave Hawley on February 06, 2001 12:31 PM

Hi J Davis

You could use an array for this, but I wouldn't. Use the DGET instead, much better.

Below the appropriate copied column heading place:
44444 and 2

Then use:
=DGET(A1:C10000,3,E1:F2)

3 is the Column position in your table you want the result from.

E1:F2 is you copied headings with the 44444 and 2 below the appropriate headings.

Hope this helps

Posted by Tim Francis-Wright on February 06, 2001 1:51 PM

DGET, though has two failings: if there are multiple
matches, it returns an error. Also, using
DGET requires filling in the criteria range,
which makes it less useful if there are many
DGETs to DGET. (Why Lotus's database functions
work so much better than Excel's is a mystery.)

It may work great in J Davis's case, but if there
is the possibility of multiple hits, VLOOKUP
may be better.

One easy way to use VLOOKUP is to make another
column [here, to the right of column B, so the
database now has columns A:D]. Have C2 = A2&B2
and copy that down as far as necessary.
Now, VLOOKUP("44444"&"2",C2:D10000,2) will return
the [first] match for 44444 and 2.

Posted by Dave Hawley on February 06, 2001 2:44 PM

Tim

You are correct of course that DGET returns a error if there is more than one matching criteria.
But Wrong in your assesment of having to fill in a criteria range. All you need is a Drop down list of both Columns in a Data Validation list, can't get much easier than that.

If you want the best way of all, then I would use a Pivot Table with "Account" and "Center" in the Page Field and "Amount" in the Data and Row Field.

Dave

Posted by Mark W. on February 06, 2001 2:45 PM

J", let's say that your lookup table is in cells A4:C15, your ACCOUNT lookup value, 44444, is in cell A1, and your CENTER lookup value, 2, is in cell A2. Use the following formula to find the corresponding AMOUNT:

=OFFSET(INDEX(A4:C15,MATCH(A1,A4:A15,0),1),A2-1,2)

Posted by J Davis on February 06, 2001 8:15 PM

Jamal Davis

Thank you gentleman for your help. However there is a catch.
A B C

22222 1 \$17
22222 10 \$1000

I Used =vlookup(a1&b1,datarange,3). HOWEVER,
I want 22222-10 \$1,000. The problem is that
It I got the value for 22222-1 (\$17--since it was
the first "match"). The solution where you add the columns (a1&b1) seems to work best but the program can not differentiate between (222221 and between 2222210). Doesn't the "&" convert things to text. I would I make these EXACTLY match.

Thank you,

Jamal Davis

Posted by Aladin Akyurek on February 06, 2001 10:28 PM

Add a 0 (or FALSE) to the VLOOKUP that Tim suggested for an exact match.

=VLOOKUP(A1&B1, DATARANGE,3,0)

Posted by Jamal Davis on February 07, 2001 5:09 AM

22222 1 \$17 22222 10 \$1000 : I Used =vlookup(a1&b1,datarange,3). HOWEVER,

Yes, I figured that out last night after looking at that. Thank you Gentleman VERY much.

Jamal

Posted by E Graham on April 18, 2001 8:56 AM

Just wanted to say a huge thank you to Tim Francis-Wright - your advice has just helped me solve a problem which has been bugging me for MONTHS!! I can't tell you what a relief it is!

E Graham.