Archive of Mr Excel Message Board

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

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

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

Jamal

E Graham.