Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is VLOOKUP what I need?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is VLOOKUP what I need?
if column D has the buyer codes and you want to total the invoice amounts in
column E, try: =SUMPRODUCT(--(D1:D7={"AA","B2","CC","D1"})*E1:E7) or if the buyer codes you are looking for are in A1:A4, you could try: =SUM(--(D1:D7=TRANSPOSE(A1:A4))*E1:E7) entered w/Cntrl+Shift+Enter. Adjust range to suit, but you can't use an entire column (eg D:D) unless you are using XL 2007. "Janett the Intermediate User" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is VLOOKUP what I need?
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. "Janett the Intermediate User" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |