Advance Vlookup function may help!!

[Guest]

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

 

[Guest]

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,codetable,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
(e-mail address removed)
remove nospam for email addy!

 

[Pete_UK]

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

 

[Biff]

Hi!

Another possibility:

=SUMPRODUCT(--(A3:A11=A18),--(B3:B11=B18),C3:C11)

Biff

 

[CLR]

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

 

[CLR]

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

 

[Guest]

Dear All,

Many thanks.It solve my problem and headed now..

Have agood week end,

Best Regards

PA