Back to Forms in Excel VBA archive index

Back to archive home

I need to look up match data in two columns w/ vlookup

ACCOUNT CENTER AMOUNT

22222 1 $25

22222 2 $77

22222 3 $16

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 | ||||

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

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.

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

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)

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

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

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

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

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.

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.