| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup where column data does not match
Hi,
I have a problem where for example; Sheet1 column A1 contains data 12345678 23456765 Sheet2 column A1 contains data Joe Bloggs12345678 Jane Bloggs23456765 An so on and so on Can someone help me with a formul to look one set of data against the other and put the result on sheet1 A1 B1 12345678 Joe Bloggs12345678 23456765 Jane Bloggs23456765 Any help is appreciated, Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup where column data does not match
=INDEX(Sheet2!A1:A100,MATCH("*"&A1,Sheet2!A1:A100, 0))
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. NTK wrote: Hi, I have a problem where for example; Sheet1 column A1 contains data 12345678 23456765 Sheet2 column A1 contains data Joe Bloggs12345678 Jane Bloggs23456765 An so on and so on Can someone help me with a formul to look one set of data against the other and put the result on sheet1 A1 B1 12345678 Joe Bloggs12345678 23456765 Jane Bloggs23456765 Any help is appreciated, Thanks -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Lookup where column data does not match
thanks Dave, that appears to be working
"Dave Peterson" wrote: =INDEX(Sheet2!A1:A100,MATCH("*"&A1,Sheet2!A1:A100, 0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. NTK wrote: Hi, I have a problem where for example; Sheet1 column A1 contains data 12345678 23456765 Sheet2 column A1 contains data Joe Bloggs12345678 Jane Bloggs23456765 An so on and so on Can someone help me with a formul to look one set of data against the other and put the result on sheet1 A1 B1 12345678 Joe Bloggs12345678 23456765 Jane Bloggs23456765 Any help is appreciated, Thanks -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup with 2 columns of data to match | Excel Worksheet Functions | |||
| Index/Match - Lookup based on multiple column criteria | Excel Worksheet Functions | |||
| 2 column lookup - match to date range | Excel Worksheet Functions | |||
| The match and lookup functions can find literal data but not the same data referenced from a cell | Excel Discussion (Misc queries) | |||
| Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
Linear Mode
