data matching

simonf

Board Regular
Joined
Jun 18, 2002
Messages
207
Book3
ABCDE
1JANFEBMARAPR
2APPLE(APO)42654566
3BANANA(BAN)26254535
4CHERRY(CHY)28349878
5ORANGE(ORG)12786348
Sheet1
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

simonf

Board Regular
Joined
Jun 18, 2002
Messages
207
Hi, I can't quite figure out what formula to use if I want to return the value of APO in Jan. Since APO is only part of the string in A2, I was thinking of the SEARCH function but just can't quite put it together. THanks.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Are we playing Jeopardy... and, we have to supply an "answer" in the form of a question?
 

simonf

Board Regular
Joined
Jun 18, 2002
Messages
207
Hi, I can't quite figure out what formula to use if I want to return the value of APO in Jan. Since APO is only part of the string in A2, I was thinking of the SEARCH function but just can't quite put it together. THanks.
 

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654

ADVERTISEMENT

Book1
ABCDEFGHIJKLMN
1JANFEBMARAPR42
2APPLE(APO)42654566
3BANANA(BAN)26254535
4CHERRY(CHY)28349878
5ORANGE(ORG)12786348
6
7
8
9
Sheet1
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Select A1:E5 and name it Table (using e.g., the Name Box).

Enter APPLE (APO) in A9.

Enter JAN in B9.

In C9 enter:

=INDEX(Table,MATCH(CHAR(42)&A9&CHAR(42),INDEX(Table,0,1),0),MATCH(B9,INDEX(Table,1,0),0))

which is as swift as you can get.

Note. CHAR(42) is a *. The way the lookup value is set up for the first MATCH use it for clarity.

Aladin
 

simonf

Board Regular
Joined
Jun 18, 2002
Messages
207

ADVERTISEMENT

Ok, I inserted an extra blank roll A and use this formula to return the value matching APO but still can't figure out how to match the month yet.
This message was edited by simonf on 2002-10-15 17:17
 

Forum statistics

Threads
1,144,510
Messages
5,724,782
Members
422,578
Latest member
annsalinas

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