data matching

[simonf]

Book3
	A	B	C	D	E
1		JAN	FEB	MAR	APR
2	APPLE(APO)	42	65	45	66
3	BANANA(BAN)	26	25	45	35
4	CHERRY(CHY)	28	34	98	78
5	ORANGE(ORG)	12	78	63	48
Sheet1

 

[simonf]

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.]

Are we playing Jeopardy... and, we have to supply an "answer" in the form of a question?

 

[simonf]

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.]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		JAN	FEB	MAR	APR			42
2	APPLE(APO)	42	65	45	66
3	BANANA(BAN)	26	25	45	35
4	CHERRY(CHY)	28	34	98	78
5	ORANGE(ORG)	12	78	63	48
6
7
8
9
Sheet1

 

[Aladin Akyurek]

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]

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

 

[simonf]

Just beat me to it. Thanks Aladin.

 

[simonf]

Thanks Mark.