Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup between values | Excel Discussion (Misc queries) | |||
combine multiple values in a table into one cell using vlookup? | Excel Worksheet Functions | |||
VLOOKUP using another table for comparison values | Excel Worksheet Functions | |||
use vlookup or other to find the nearest values (<) or interpola | Excel Worksheet Functions | |||
Vlookup on a worksheet with similar values | Excel Worksheet Functions |