Help on cross referencing

[dolphn22]

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>

 

[jamtay317]

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]

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]

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]

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]

I'm sorry I was worng use this


Excel 2012

	A	B	C	D
1	AMERICAN EXPRESS SETTLEMENT 131227 1429529449		1429529557	2
2	THE WOODLAND1429529557		1429529449	1

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

Sheet1​

Array Formulas Cell Formula 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>