Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 36
Default vlookup with 2 values

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default vlookup with 2 values

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 A1:D200, 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(),IND EX(Sheet2!$C$1:$C$3,MATCH(A1&B1,Sheet2!$D$1:$D$3,0 )))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Sally" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default vlookup with 2 values

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:D$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

Sally wrote:
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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10,593
Default vlookup with 2 values

=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)

"Sally" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 195
Default vlookup with 2 values

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

Sally wrote:
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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup between values [email protected] Excel Discussion (Misc queries) 1 August 16th 06 09:35 AM
combine multiple values in a table into one cell using vlookup? Liam S. Excel Worksheet Functions 0 July 27th 06 09:21 PM
VLOOKUP using another table for comparison values Karen S. Excel Worksheet Functions 3 June 15th 06 09:56 PM
use vlookup or other to find the nearest values (<) or interpola Rodney Excel Worksheet Functions 4 April 5th 06 09:21 PM
Vlookup on a worksheet with similar values GWHITE1 Excel Worksheet Functions 3 December 31st 05 05:16 PM


All times are GMT +1. The time now is 11:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"