| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
find a value at an intersection?
I have the following data:
I need to lookup the part number and trancode in Worksheet2 and return the quantity to worksheet1. Worksheet1 A B C D E F G H I J TraneCodes 2 6 8 12 28 36 54 60........................ 1 PART NUMBER 2 003-0623-00 3 005-0030-00 4 005-0168-00 5 005-0320-00 6 005-0420-00 7 005-0539-00 8 005-0798-01 9 005-7013-00 Worksheet2: A B C D 1 Part# TranCode RecvPlt Qty 2 003-0018-01 28 7 10 3 003-0018-01 40 7 10 4 003-0021-00 8 54 25 5 003-0021-04 4 63 6 003-0021-04 6 11 66 7 003-0021-04 11 1 8 003-0059-00 4 649 9 003-0059-00 22 22 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find a value at an intersection?
=IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$ 1=Sheet2!$B$2:$B$9),0)),""
,INDEX(Sheet2!$B$1:$B$9,MATCH(1,(Sheet1!$A2=Sheet2 !$A$1:$A$9)*(B$1=Sheet2!$B $2:$B$9),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. copy down and across, but I get no matches with your data. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ellen G." wrote in message ... I have the following data: I need to lookup the part number and trancode in Worksheet2 and return the quantity to worksheet1. Worksheet1 A B C D E F G H I J TraneCodes 2 6 8 12 28 36 54 60........................ 1 PART NUMBER 2 003-0623-00 3 005-0030-00 4 005-0168-00 5 005-0320-00 6 005-0420-00 7 005-0539-00 8 005-0798-01 9 005-7013-00 Worksheet2: A B C D 1 Part# TranCode RecvPlt Qty 2 003-0018-01 28 7 10 3 003-0018-01 40 7 10 4 003-0021-00 8 54 25 5 003-0021-04 4 63 6 003-0021-04 6 11 66 7 003-0021-04 11 1 8 003-0059-00 4 649 9 003-0059-00 22 22 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find a value at an intersection?
Thank Bob - This doesn't reference my quantity column though?
I need it to return the quantity of parts when both conditions agree. "Bob Phillips" wrote: =IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$ 1=Sheet2!$B$2:$B$9),0)),"" ,INDEX(Sheet2!$B$1:$B$9,MATCH(1,(Sheet1!$A2=Sheet2 !$A$1:$A$9)*(B$1=Sheet2!$B $2:$B$9),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. copy down and across, but I get no matches with your data. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ellen G." wrote in message ... I have the following data: I need to lookup the part number and trancode in Worksheet2 and return the quantity to worksheet1. Worksheet1 A B C D E F G H I J TraneCodes 2 6 8 12 28 36 54 60........................ 1 PART NUMBER 2 003-0623-00 3 005-0030-00 4 005-0168-00 5 005-0320-00 6 005-0420-00 7 005-0539-00 8 005-0798-01 9 005-7013-00 Worksheet2: A B C D 1 Part# TranCode RecvPlt Qty 2 003-0018-01 28 7 10 3 003-0018-01 40 7 10 4 003-0021-00 8 54 25 5 003-0021-04 4 63 6 003-0021-04 6 11 66 7 003-0021-04 11 1 8 003-0059-00 4 649 9 003-0059-00 22 22 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find a value at an intersection?
My mistake
=IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$ 1=Sheet2!$B$2:$B$9),0)),"" ,INDEX(Sheet2!$D$1:$D$9,MATCH(1,(Sheet1!$A2=Sheet2 !$A$1:$A$9)*(B$1=Sheet2!$B $2:$B$9),0))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ellen G." wrote in message ... Thank Bob - This doesn't reference my quantity column though? I need it to return the quantity of parts when both conditions agree. "Bob Phillips" wrote: =IF(ISNA(MATCH(1,(Sheet1!$A2=Sheet2!$A$1:$A$9)*(B$ 1=Sheet2!$B$2:$B$9),0)),"" ,INDEX(Sheet2!$B$1:$B$9,MATCH(1,(Sheet1!$A2=Sheet2 !$A$1:$A$9)*(B$1=Sheet2!$B $2:$B$9),0))) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. copy down and across, but I get no matches with your data. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Ellen G." wrote in message ... I have the following data: I need to lookup the part number and trancode in Worksheet2 and return the quantity to worksheet1. Worksheet1 A B C D E F G H I J TraneCodes 2 6 8 12 28 36 54 60........................ 1 PART NUMBER 2 003-0623-00 3 005-0030-00 4 005-0168-00 5 005-0320-00 6 005-0420-00 7 005-0539-00 8 005-0798-01 9 005-7013-00 Worksheet2: A B C D 1 Part# TranCode RecvPlt Qty 2 003-0018-01 28 7 10 3 003-0018-01 40 7 10 4 003-0021-00 8 54 25 5 003-0021-04 4 63 6 003-0021-04 6 11 66 7 003-0021-04 11 1 8 003-0059-00 4 649 9 003-0059-00 22 22 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
find a value at an intersection?
Hi Ellen,
I would try hlookup() to search for "TraneCodes", (add a extra Row below TraneCodes to indicate the row number); use vlookup() to search for the Partnumber and use the "row-result" from hlookup() as ofset ... Clear enough? "Ellen G." wrote: I have the following data: I need to lookup the part number and trancode in Worksheet2 and return the quantity to worksheet1. Worksheet1 A B C D E F G H I J TraneCodes 2 6 8 12 28 36 54 60........................ 1 PART NUMBER 2 003-0623-00 3 005-0030-00 4 005-0168-00 5 005-0320-00 6 005-0420-00 7 005-0539-00 8 005-0798-01 9 005-7013-00 Worksheet2: A B C D 1 Part# TranCode RecvPlt Qty 2 003-0018-01 28 7 10 3 003-0018-01 40 7 10 4 003-0021-00 8 54 25 5 003-0021-04 4 63 6 003-0021-04 6 11 66 7 003-0021-04 11 1 8 003-0059-00 4 649 9 003-0059-00 22 22 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Intersection of two lines | Charts and Charting in Excel | |||
| Find and Replace | Excel Worksheet Functions | |||
| Code needed to find records from bottom up | Excel Discussion (Misc queries) | |||
| find a cells from a range of cell | Excel Worksheet Functions | |||
| Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Linear Mode
