Help on cross referencing

dolphn22

Board Regular
Joined
Aug 22, 2008
Messages
82
Hello all,

I have an item in cell A1 on tab1, the description is

AMERICAN EXPRESS SETTLEMENT 131227 1429529449 THE WOODLAND1429529449

I have a list on tab 2 in column A. One of the items in the list is 1429529449.

What I want to do.............. In cell B1 on tab1, I want a formula that will be able to cross reference cell A1 to the list on tab2, and if any number within the description matches any item in the list on tab2, it will return the value on tab 2 in column B in the same row as the match.

The problem I am having is that the description I want to match to the list is not an exact match, it has a lot of other miscellaneous data. I have been stuck on this for a very long time. Any guidance would be awesome!

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
this is very doable, is the data on sheet 1 all different?
how may rows is it in both sheets?
can you please provide some sample data?
 

dolphn22

Board Regular
Joined
Aug 22, 2008
Messages
82
Yes, the # in cell A1 that I will be matching to is always in a different place, may be 10 characters in, 12 characters in, etc. the # of rows varies, but for example sake let's say column A on tab 1 is 100 down, and I want to do a formula in cell B1 to match A1 on tab 1 to the list in tab2, and then pull column B on tab 2. At that point I would then drag it down to the bottom of the list....................... on tab 2 the list is 1000 rows, so both rows in column A and column B are 1000 down. If any # in tab 2 column A can be found within the description in cell A1, I want it to pull the field in column B from tab2.

To say it another way, if they matched exactly, I would not have a problem. My formula in B1 would be:
=INDEX('Tab2'!$1:$1048576,MATCH('Tab1'!A1,'Tab2'!A1:A1000,0),2)


But, they do not match exactly, so doing the same exercise to get the data is killing me.

Please, please help! I am not sure what the best way is for me to copy example data & files on here, but can try if that is what you need to help me.

Trust me, any help at all you can provide will be very greatly apprciated!
</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

<COLGROUP><COL style="WIDTH: 96pt" width=128><TBODY>
</TBODY>

<COLGROUP><COL style="WIDTH: 96pt" width=128><TBODY>
</TBODY>
 

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
I do understand, and the anwser is it cannot be done with a formula, you will have to use VBA, that is the reason that i'm asking information about the data that looks like this

AMERICAN EXPRESS SETTLEMENT 131227 1429529449 THE WOODLAND1429529449.
can you please add some data from that looks like this.
 

dolphn22

Board Regular
Joined
Aug 22, 2008
Messages
82
I had a formula that did do this a few years back (unfortunately, i lost it). I think it was maybe a lookup / match combination (I can't exactly recall), but I do remember somewhere in the formula I had to do a weird 2^124, or something like that..... can't recall the numbers exactly but am sure it was x^x ..................... do you know anything about what that would mean within a formula? I am trying to piece it together, but am struggling big time. The good news is I had a formula once that did this, positive of that, bad news is I am not sure I will ever get it again.

VBA would work but I would like to figure out the formula if I can. if the above rings any kind of bell for you, I would definitely like your thoughts.

Either way, I want to thank you for discussing this with me.
 

jamtay317

Well-known Member
Joined
Mar 6, 2013
Messages
769
I'm sorry I was worng use this


Excel 2012
ABCD
1AMERICAN EXPRESS SETTLEMENT 131227 142952944914295295572
2THE WOODLAND142952955714295294491

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
D1{=MATCH(TRUE,ISNUMBER(SEARCH(C1,Sheet1!$A$1:$A$3)),0)}
D2{=MATCH(TRUE,ISNUMBER(SEARCH(C2,Sheet1!$A$1:$A$3)),0)}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top