Advanced lookup or sorting question!

nhawthorne

New Member
Joined
Aug 17, 2007
Messages
13
I have a large amount of raw data that I import via a text file into Excel. Here is an example of the data in order for me to illustrate my question.

USD COUPON ENTRY PREVIOUSLY DELAYED
S/N 0243**** dfssdfsdf S 9.37500 07/02/11 USD
REC. 23/07/07 DUE 07/08/07 CPN.AMNT USD 23.437500 Q
NAR. FUNDS RECEIVED

SCA. USD 10860000
-----------------------------------------------------
NET USD 254531.25+ 07/08/07
-----------------------------------------------------
DACE REFERENCE IS *******


On a seperate workbook I have a list of money amounts which I need to find the S/N # for. So if you look above, there is a money amount and a S/N within the raw data. My question is, what can I do to take the money amount from the first workbook and search for that amount in the raw data, then return the S/N number that is within that same area? And do it via a macro or advanced formula so that I can do I many times over?

Keep in mind, even when I delimit the data, it's all in different columns and rows, and never consistently spaced.

Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Joined
Jul 30, 2006
Messages
3,656
nhawthorne,

Welcome to the board.

With the raw data above, what is the value from the 'money amounts' sheet?

And, what is the 'S/N' in the data above?

Have a great day,
Stan
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
Book1
ABCD
1USDCOUPONENTRYPREVIOUSLYDELAYED
2S/N0243****dfssdfsdfS9.3750007/02/11USD9.37500S/N0243****
3REC.23/07/07DUE07/08/07CPN.AMNTUSD23.437500Q10.37500S/N0244****
4NAR.FUNDSRECEIVED
5
6SCA.USD10860000
7-----------------------------------------------------
8NETUSD254531.25+07/08/07
9-----------------------------------------------------
10DACEREFERENCEIS*******
11USDCOUPONENTRYPREVIOUSLYDELAYED
12S/N0244****dfssdfsdfS10.3750007/02/11USD
13REC.23/07/07DUE07/08/07CPN.AMNTUSD23.437500Q
14NAR.FUNDSRECEIVED
15
16SCA.USD10860000
17-----------------------------------------------------
18NETUSD254531.25+07/08/07
19-----------------------------------------------------
20DACEREFERENCEIS*******
Sheet1
/

Formula in D2 (and dragged down):

=LEFT(INDEX($A$1:$A$20,MATCH(9.99999999999999E+307,IF(ISNUMBER(FIND(C2,$A$1:$A$20)),ROW($A$1:$A$20)))),12)

Confirmed w/Ctrl+Shift+Enter
 

ColdFlo

New Member
Joined
Aug 19, 2007
Messages
1
hello

Hi, I'm noob can you tell me chonological english translation of that statement so I can know how its said? Learning how things are said helps me visualize. Then I can understand the mind that produced the earliest renditions of this modern syntax. Also, what is 9.9999999999999999E + 307? Are that many nines necessary?
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976

ADVERTISEMENT

First, let me explain all of those 9s and how it relates to the MATCH function.

9.999999999999999E+307 is the largest number Excel can recognize. The MATCH function above is looking for an approximate match to this big number. So, the closest match to 9.999999999999999E+307 will be assumed to be the last number in the look up range because MATCH assumes the list is sorted in ascending order.

In other words, if I had a list:

2
3
1

the MATCCH function assumes this list is actually sorted in ascending order (even though in reality it is not) and that the last number in the list MUST be the closest match to 9.999999999999999E+307.

With that understood:

Find the value in C2 in the specified range. If the FIND function returns a number then return the row number of where the value in C2 was found. Next, MATCH 9.999999999999999E+307 using the rows returned as the lookup range...this will return the row number that we need. The index function will use that row to return the cell address. Then, return the first 12 characters from the left for the cell address returned.

That's it. If you have Excel 2003 or later you can use the Formula Auditing toolbar to step through the calculation process and see how everything works together...very useful.

As well, after review, this would be more efficient:

=LEFT(INDEX($A$1:$A$20,MAX(IF(ISNUMBER(FIND(C2,$A$1:$A$20)),ROW($A$1:$A$20)))),12)

Array entered.
 

nhawthorne

New Member
Joined
Aug 17, 2007
Messages
13
ExcelChampion,

I've tried using your formula, but I cannot seem to come up with the correct answer. The search criteria for the money amount comes from another workbook and the answer needs to be returned to that workbook. That money amount is then found below the S/N line in the raw data. So, below, the number that I need to search for is in BOLD, and the number that I need to return is in BOLD and underlined.

USD COUPON ENTRY PREVIOUSLY DELAYED
S/N 0243**** dfssdfsdf S 9.37500 07/02/11 USD
REC. 23/07/07 DUE 07/08/07 CPN.AMNT USD 23.437500 Q
NAR. FUNDS RECEIVED

SCA. USD 10860000
-----------------------------------------------------
NET USD 254531.25+ 07/08/07
-----------------------------------------------------
DACE REFERENCE IS *******

How could I edit your formula for these specifications?

N
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976

ADVERTISEMENT

Does this help:

=RIGHT(LEFT(INDEX($A$1:$A$20,MAX(IF(ISNUMBER(FIND(C2,$A$1:$A$20)),ROW($A$1:$A$20)))-6),12),8)

Confirmed w/Ctrl+Shift+Enter
 

nhawthorne

New Member
Joined
Aug 17, 2007
Messages
13
Oh, and just in case this matters... There is a plus sign after each money amount in the raw data, and the cells are delimited as text fields. Where as within the other workbook that I am searching from, the money amounts are all formatted as currencies. I would assume this might have an affect on the search, but I could be wrong.

N
 

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
You need to change the ranges in the formula to suit your needs, but ultimately it should something like:

=RIGHT(LEFT(INDEX($A$1:$A$35000,MAX(IF(ISNUMBER(FIND(C2,$A$1:$A$35000)),ROW($A$1:$A$35000)))-6),12),8)

Array entered.

You will need to change column references as well as workbook references.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,554
Messages
5,596,814
Members
414,104
Latest member
imamalidadashzada

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