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
|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
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.
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.
OzGrid Business Applications
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.
Add a 0 (or FALSE) to the VLOOKUP that Tim suggested for an exact match.
Yes, I figured that out last night after looking at that. Thank you Gentleman VERY much.