Is VLOOKUP what I need?

[Janett the Intermediate User]

I'm not even sure if VLOOKUP is what I should use...

The spreadsheet I am working with has multiple columns containing accounts
payable information: invoice numbers, costs, invoice dates, and a code for
the buyer that purchased the items on the invoice.

The information is coming from another program which someone kindly
transfers to excel for me. The problem is that each buyer has more than one
"buyer code"...for example John Smith is buyer code AA, B2, CC, and D1. So -
right now - all I can sort by is this buyer code - which isn't very helpful
if I am looking for total invoices for a buyer. Some buyers have three codes,
some just one. There are about twenty buyers, and about thirty or fourty
codes. I would like to use the buyer code to assign the buyer name in the
same row. I don't think VLOOKUP is what I need...and plus I can't get it to
work.

Any suggestions? There are too many buyers/code for nesting IF functions or
even a concatenate function. I think, also the use of the type of buyer code
(AA, B1, C2) confuses the function.

 

[JMB]

if column D has the buyer codes and you want to total the invoice amounts in
column E, try:

=SUMPRODUCT(--(D17={"AA","B2","CC","D1"})*E1:E7)

or if the buyer codes you are looking for are in A1:A4, you could try:

=SUM(--(D17=TRANSPOSE(A1:A4))*E1:E7)

entered w/Cntrl+Shift+Enter. Adjust range to suit, but you can't use an
entire column (eg D) unless you are using XL 2007.

 

[Janett the Intermediate User]

Nevermind! I figured it out! I used just the plain-old "LOOKUP" code...

This is the formula I used:

=LOOKUP(CELL_NAME,TABLE_ARRAY)

If you use the LOOKUP function, EXCEL will not "set" the table array if you
fill other cells with the formula - so I used "$"...this is what my formula
actually looks like:

=LOOKUP(C1102,TABLES!$E$4:$F$83)

Yeah!! I am so happy - this saves me HOURS of data entry!! I have to say -
sorry MS - but the directions in EXCEL help were too complicated! But, the
function 'wizard' helped - although still a tad complicated.

Oh, and the column in the lookup table (which was BUYER CODE in this case)
that matches the information in your spreadsheet must be in ascending order.
I'm sure everyone but me knows that... So, if you are looking for name for
buyer A1 (like I was), the column that uses A1 is the one you sort.