Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advance Vlookup function may help!!
Dear All,
I try to use the simple vlookup function as follows: Here is the source of info/list to be looked up: Col A Col B Col C Row-1 Lookup List/Source of Info Row-2 ID Code Cost Row-3 111 A $100.00 Row-4 112 A $150.00 Row-5 114 A $200.00 Row-6 117 A $250.00 Row-7 112 C $15.00 Row-8 114 C $20.00 Row-9 117 C $25.00 Row-10 etc Row-11 Col A Col B Col C Report/where Vlookup function needed ID Code Cost 112 C $150.00 ====== =VLOOKUP(A18,$A$3:$C$11,3,FALSE) Come with Result $150, suppose $15 as I try to lookup Column A (ID) and Col B(Code) -with combination 112 and C Not Combination 112 and A, which has result $150 114 A $200.00 Is there anyone can help me with "advance vlookup function" which can "lookup" column A (ID) and Column B(Code) together and result with $15 instead of $150? Many thanks for your help, Warm Regards PA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advance Vlookup function may help!!
Highlight Column C and Edit | Insert to create a new column C. Enter
this formula in the new C3: =A3 & B3 and copy down to C11. Amend your formula in what is now D18 (used to be C18) to: =VLOOKUP(A18&B18,$C$3:$D$11,2,FALSE) and copy this down as necessary. You can hide the new column C if you want your sheet to look like it did before. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advance Vlookup function may help!!
Hi!
Another possibility: =SUMPRODUCT(--(A3:A11=A18),--(B3:B11=B18),C3:C11) Biff "PA" wrote in message ... Dear All, I try to use the simple vlookup function as follows: Here is the source of info/list to be looked up: Col A Col B Col C Row-1 Lookup List/Source of Info Row-2 ID Code Cost Row-3 111 A $100.00 Row-4 112 A $150.00 Row-5 114 A $200.00 Row-6 117 A $250.00 Row-7 112 C $15.00 Row-8 114 C $20.00 Row-9 117 C $25.00 Row-10 etc Row-11 Col A Col B Col C Report/where Vlookup function needed ID Code Cost 112 C $150.00 ====== =VLOOKUP(A18,$A$3:$C$11,3,FALSE) Come with Result $150, suppose $15 as I try to lookup Column A (ID) and Col B(Code) -with combination 112 and C Not Combination 112 and A, which has result $150 114 A $200.00 Is there anyone can help me with "advance vlookup function" which can "lookup" column A (ID) and Column B(Code) together and result with $15 instead of $150? Many thanks for your help, Warm Regards PA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advance Vlookup function may help!!
CONCATENATE your two columns together into a new column at the left of your
table, so you will be looking up 112A and 112C instead of 112 in cone column and A in another, and 112 in one column and C in another, etc etc...... hth Vaya con Dios, Chuck, CABGx3 "PA" wrote in message ... Dear All, I try to use the simple vlookup function as follows: Here is the source of info/list to be looked up: Col A Col B Col C Row-1 Lookup List/Source of Info Row-2 ID Code Cost Row-3 111 A $100.00 Row-4 112 A $150.00 Row-5 114 A $200.00 Row-6 117 A $250.00 Row-7 112 C $15.00 Row-8 114 C $20.00 Row-9 117 C $25.00 Row-10 etc Row-11 Col A Col B Col C Report/where Vlookup function needed ID Code Cost 112 C $150.00 ====== =VLOOKUP(A18,$A$3:$C$11,3,FALSE) Come with Result $150, suppose $15 as I try to lookup Column A (ID) and Col B(Code) -with combination 112 and C Not Combination 112 and A, which has result $150 114 A $200.00 Is there anyone can help me with "advance vlookup function" which can "lookup" column A (ID) and Column B(Code) together and result with $15 instead of $150? Many thanks for your help, Warm Regards PA |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advance Vlookup function may help!!
CONCATENATE your two columns together into a new column at the left of your
table, so you will be looking up 112A and 112C instead of 112 in cone column and A in another, and 112 in one column and C in another, etc etc...... hth Vaya con Dios, Chuck, CABGx3 "PA" wrote in message ... Dear All, I try to use the simple vlookup function as follows: Here is the source of info/list to be looked up: Col A Col B Col C Row-1 Lookup List/Source of Info Row-2 ID Code Cost Row-3 111 A $100.00 Row-4 112 A $150.00 Row-5 114 A $200.00 Row-6 117 A $250.00 Row-7 112 C $15.00 Row-8 114 C $20.00 Row-9 117 C $25.00 Row-10 etc Row-11 Col A Col B Col C Report/where Vlookup function needed ID Code Cost 112 C $150.00 ====== =VLOOKUP(A18,$A$3:$C$11,3,FALSE) Come with Result $150, suppose $15 as I try to lookup Column A (ID) and Col B(Code) -with combination 112 and C Not Combination 112 and A, which has result $150 114 A $200.00 Is there anyone can help me with "advance vlookup function" which can "lookup" column A (ID) and Column B(Code) together and result with $15 instead of $150? Many thanks for your help, Warm Regards PA |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Advance Vlookup function may help!!
Dear All,
Many thanks.It solve my problem and headed now.. Have agood week end, Best Regards PA "CLR" wrote: CONCATENATE your two columns together into a new column at the left of your table, so you will be looking up 112A and 112C instead of 112 in cone column and A in another, and 112 in one column and C in another, etc etc...... hth Vaya con Dios, Chuck, CABGx3 "PA" wrote in message ... Dear All, I try to use the simple vlookup function as follows: Here is the source of info/list to be looked up: Col A Col B Col C Row-1 Lookup List/Source of Info Row-2 ID Code Cost Row-3 111 A $100.00 Row-4 112 A $150.00 Row-5 114 A $200.00 Row-6 117 A $250.00 Row-7 112 C $15.00 Row-8 114 C $20.00 Row-9 117 C $25.00 Row-10 etc Row-11 Col A Col B Col C Report/where Vlookup function needed ID Code Cost 112 C $150.00 ====== =VLOOKUP(A18,$A$3:$C$11,3,FALSE) Come with Result $150, suppose $15 as I try to lookup Column A (ID) and Col B(Code) -with combination 112 and C Not Combination 112 and A, which has result $150 114 A $200.00 Is there anyone can help me with "advance vlookup function" which can "lookup" column A (ID) and Column B(Code) together and result with $15 instead of $150? Many thanks for your help, Warm Regards PA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup worksheet function | Excel Worksheet Functions | |||
Vlookup with function in table_array | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
VLOOKUP Function using Data Ranges. | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions |