vlookup on multiple values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

samuel - 07 May 2008 15:31 GMT

I have the following data in 3 columns

A    B    C
TOP    BULB    1
TOP    SCREW    5
TOP    LIGHT    1
TOP    LAMP    1
TOP    PAPER    1
BOTTOM    BULB    5
BOTTOM    SCREW    4
BOTTOM    LIGHT    2
BOTTOM    LAMP    8
BOTTOM    PAPER    7

I want to be able to first match against the value in column a then find the
match in column b once it finds both match then return the result of column
3.

Reply to this Message

ND Pard - 07 May 2008 15:44 GMT

Insert a new column A.

In the new column A concatenate the data from what is now column B and
column C. Example of a row two formula:

=B2&" "&C2

Now you can use a vLookUp function on Column A with an offset of 4 to return
data from what is now column D.

Good Luck.

> I have the following data in 3 columns
>
[quoted text clipped - 13 lines]
> match in column b once it finds both match then return the result of column
> 3.

Reply to this Message

Bernard Liengme - 07 May 2008 15:47 GMT

1) If you can add a new A column:
In new A1 use =B1&C1
With BOTTOM in G1 and SCREW in H1, use =VLOOKUP(G1&H1,A1:D10,4,FALSE)

2) If you cannot insert, then add a new column (I will assume in D but
anywhere is OK)
In D1 use =A1&B1
With BOTTOM in G1 and SCREW in H1, use =INDEX(D1:D10,MATCH(G1&H1,A1:A10,0))

best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

>I have the following data in 3 columns
>
[quoted text clipped - 15 lines]
> column
> 3.

Reply to this Message

Teethless mama - 07 May 2008 16:01 GMT

=SUMPRODUCT(--(A1:A100="Top"),--(B1:B100="Bulb),C1:C100)

> I have the following data in 3 columns
>
[quoted text clipped - 13 lines]
> match in column b once it finds both match then return the result of column
> 3.

Reply to this Message