vlookup with 2 values

[Sally]

Hi,
I want to vlookup in sheet 1 col A & col B - where both these values appear
in the same row in sheet 2, give me the data in the 3rd column.

SHEET1
A B C D
1 ONE 666 123 SECURITY
2 FIVE 818 682 NUMBER

SHEET2
A B C
1 ONE 666 4,876.12
2 ONE 818 6,821.11
3 FIVE 818 1,211.01

AFTER VLOOKUP PERFORMED - RESULT SHOULD LOOK LIKE THIS;
SHEET1
A B C D E
1 ONE 666 123 SECURITY 4,876.12
2 FIVE 818 682 NUMBER 1,211.01
3 SIX 818 682 SECURITY N/A

Thanks
Sally

 

[Bernard Liengme]

Hi Sally,
If you can insert a new A column in Sheet2 and use =B1&C1 (giving ONE611 in
A1); copy down column.
On Sheet1 use =VLOOKUP(A1&B1,Sheet2!$A$1:$D$3,4,FALSE)
Of course, the ranges need not end at 3 but could be A1200, or whatever

If you cannot insert a new A column then in a column to the right of the
data on Sheet2 add =A1&B1 (let's say this is in column D
On Sheet 1 use
=IF(ISNA(MATCH(A1&B1,Sheet2!$D$1:$D$3,0)),NA(),INDEX(Sheet2!$C$1:$C$3,MATCH(A1&B1,Sheet2!$D$1:$D$3,0)))
best wishes

 

[Pete_UK]

Hi Sally,

Insert a new column C in Sheet2 and enter this formula in C2:

=A2&B2

and copy this formula down by double-clicking the fill handle (the
small black square in the bottom right corner of the cursor when C2 is
selected). Then enter this formula in E2 of Sheet1:

=VLOOKUP(A2&B2,Sheet2!C$2$500,2,0)

This assumes you have 500 entries in Sheet2 - adjust the range to suit.
Format the cell to suit, then copy the formula down column E for as
many entries as you have in column A.

Hope this helps.

Pete

 

[Bob Phillips]

=INDEX(Sheet2!$C$1:$C$1000,MATCH(1,(Sheet2!$A$1:$A$1000=A1)*(Sheet2!$B$1:$B$
1000=B1),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[shail]

Hi Sally,

Try this one too

=INDEX(sheet2!C$1:C$50,MATCH(A1&B1,sheet2!A$1:A$50&sheet!B$1:B$50,0),0)

Treat this one as Array entered formula.

Hope that helps

thanks,
Shail