Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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


Check out our Excel Resources

Re: VLOOKUP & MULTIPLE COLUMNS

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.

Copy your Column headings to any range.

Below the appropriate copied column heading place:
44444 and 2

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


Where A1:C1000 is your entire table, including Column headings.

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

DaveOzGrid Business Applications


Re: VLOOKUP & MULTIPLE COLUMNS

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.


Re: VLOOKUP & MULTIPLE COLUMNS

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.

Your method will of course work, but requires overloading the spreadsheet with many un-needed formulas and not very easy to modify.

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


OzGrid Business Applications


Re: VLOOKUP & MULTIPLE COLUMNS

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)


Re: VLOOKUP & MULTIPLE COLUMNS

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
I had Account Center Amount

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


Re: VLOOKUP & MULTIPLE COLUMNS

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)


Re: VLOOKUP & MULTIPLE COLUMNS

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



Re: VLOOKUP & MULTIPLE COLUMNS

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.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.