Index and match, snag?

[buzzing]

Hi,
Iam using Index and Match in my formula for column I, to Match a value in column J and index a value in I. According to the help file the following types of match can be done:
If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.


If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

I need to use match type 1, but my array values are in random order.

Is it possible to make match type 1, work with "LOOKUP array can be in any order".
If not which other function would work?
Any ideas?

 

[saurabh726]

Is this what ur looking for...??

Book3
	A	B	C	D	E	F	G	H	I	J
2	Amit	2							1	Saurabh
3	Dev	5							2	Amit
4	Mahesh	3							3	Mahesh
5	Manav	4							4	Manav
6	Saurabh	1							5	Dev
Sheet1

Saurabh...

 

[Aladin Akyurek]

...

I need to use match type 1, but my array values are in random order.

Is it possible to make match type 1, work with "LOOKUP array can be in any order"...

Why are you insisting on match-type 1, if you cannot meet its requirement: a lookup-array set in ascending order?

 

[fairwinds]

Hi,

Maybe you want:

Book1
	A	B	C	D
1
2	5	a
3	7	s	4	h
4	1	d
5	8	f
6	2	g
7	3	h
8	10	j
9	9	k
Sheet5

 

[buzzing]

Hi, thanks for the input.
Why are you insisting on match-type 1, if you cannot meet its requirement: a lookup-array set in ascending order?. Because I need the match to cope with matches equal or closest if no exact match is found.
This is my sheet . hope it helps. At I20 it should return 5 but it shows 4. I think it is because the lookup array values are random order.

2608Template.xls
	G	H	I	J
12			3.0	3.0
13	1		3.0	3.0
14	2		3.0	4.0
15	3		3.0	5.3
16	4		4.0	7.1
17	0	3.00	5.0	8.9
18	1		3.0	5.3
19	2		4.0	7.1
20	3		4.0	8.9
21	4		5.0	11.1
22	5		6.0	13.3
23	6		7.0	15.6
24	7		8.0	17.8
25	8		9.0	20.0
26	9		10.0	22.2
27	10		11.0	24.4
28	11		12.0	26.7
Sheet2

 

[Aladin Akyurek]

...I need the match to cope with matches equal or closest if no exact match is found.
This is my sheet . hope it helps. At I20 it should return 5 but it shows 4. I think it is because the lookup array values are random order.
...

Clicking in cells of your exhibit gives me a run-time error. Can you indicate what you're matching (lookup values) against which range?

 

[buzzing]

...I need the match to cope with matches equal or closest if no exact match is found.
This is my sheet . hope it helps. At I20 it should return 5 but it shows 4. I think it is because the lookup array values are random order.
...

Clicking in cells of your exhibit gives me a run-time error. Can you indicate what you're matching (lookup values) against which range?

INDEX(I$13:I19,MATCH(J20,J$13:J19,1))
This is the index part of my formula at I20, I tried posting the whole formula but it went **** up.
Can you not see the formula for Column I on the sheet?

 

[buzzing]

if this works, it should shown the full formula at I20

2608Template.xls
	G	H	I	J
12			3.0	3.0
13	1		3.0	3.0
14	2		3.0	4.0
15	3		3.0	5.3
16	4		4.0	7.1
17	0	3.00	5.0	8.9
18	1		3.0	5.3
19	2		4.0	7.1
20	3		4.0	8.9
21	4		5.0	11.1
22	5		6.0	13.3
23	6		7.0	15.6
24	7		8.0	17.8
25	8		9.0	20.0
26	9		10.0	22.2
27	10		11.0	24.4
28	11		12.0	26.7
Sheet2

 

[Aladin Akyurek]

if this works, it should shown the full formula at I20...

I still get the same error as reported in a previous reply. I believe the HtmlMaker has trouble with the formatting of the cells (underlining, etc.). When posting, strip off formats that do not convey information about the problem that you seek a solution for. Also, such postings should also include the formulas along with the cells housing them as plain text outside the image (see some posts of mine with an accompanying image).

 

[Von Pookie]

Aladin, I believe the problems are simply because the HTML Maker has already been used in this thread before buzzing posted himself. If I remember correctly, it simply has something to do with the javascript in the code not liking multiple instances-I unfortunately can't remember the technical terminology at the moment. But if you'll notice, *all* the HTML Maker posts in this thread will now result in the javascript errors.

The only workaround I know is to just post the formulas in the text of the message itself.