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

Advance Vlookup function may help!!
 

i think i would do the lookup and then apply the code afterwards something like
=VLOOKUP(A18,$A$3:$C$11,3,FALSE)*vlookup(code,code table,2,false),where the
code is A,B,C etc set up in a table ,with the corresponding multiplier in the
second column.In this case your codetable looks like this
A B
1 a 1
2 c .1
you will need an input cell for your code of A or C

your



paul

remove nospam for email addy!



"PA" wrote:

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

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

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

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

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

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