hlookup / index / match picking range that number occurs in

[kbetteley]

I want to use a lookup command to pick the column that a particular number refers to, the columns, rather than being numbers, are ranges.

i.e.

I have a list of numbers between 1 and 10. I have a lookup table. That lookup table has the ranges 1-3, 4-5, 6-7 and 8-10.

I've got a 4x4 grid on a lookup sheet, with labels along two edges. One set of those lables is an easy text match. The other is the ranges above.
I want to use something to cross reference so that with the X and Y "co-ordinates" i can return the value they point at. (It's a form of risk matrix).

Any ideas?

 

[prabby25101981]

have you tried using the address() function? Try help on this in excel. You can always use Indirect to create a cell reference to address created by ADDRESS().

 

[kbetteley]

That's not quite what I want to do.

I basically want to use the following grid:



1 4 6 8
2 5 7 9
3 10
VL 4 6 8 10
L 3 5 7 9
U 2 5 6 8
VU 1 2 4 6

If I have L and 7 I Want to return 7 (where the crossreference is).
If I have VL and 3, I want to return 4.

Is that any clearer?

 

[prabby25101981]

Something like this -

=INDEX($B$3:$E$8,MATCH(G2,$A$3:$A$8,0),MATCH(H2,$B$2:$E$2,1))

taking your first example, G2 is where L is and H2 is where 7 is. B3:B8 is the result array, A3:A8 is where you are finding L, and B2:E2 is where you are finding 7.

Hope this helps !

 

[kbetteley]

Thanks for decoding that.
Still not working...

Basically, I Can get the index (Match...etc) to work up until it tries to pick the column number using the array (rather than a simple range).
It just returns "N/A" rather than the column number.

It's obviously something to do with having multiple rows in the Array. I don;t care3 which row it's in, I just want it to find the value and give me the column number... Any thoughts?

 

[prabby25101981]

It will return #N/A only when it is not able to find anything. Since your range above is 1 4 6 8, there are numbers missing in between. To get a relevant answer, you will have to use TRUE in the range_lookup instead of FALSE. Which is why my column picking MATCH() has 1 there and not 0. Please take a look -

=INDEX($B$3:$E$8,MATCH(G2,$A$3:$A$8,0),MATCH(H2,$B$2:$E$2,1))

In case you can post some data here, it will be helpful for me to understand...

 

[jasonb75]

prabby, you were right first time, the address function is the way to go.

Excel Workbook
	A	B	C	D	E	F
6			1	4	6	8
7			2	5	7	9
8			3	10
9		VL	4	6	8	10
10		L	3	5	7	9
11		U	2	5	6	8
12		VU	1	2	4	6
13
14
15		Ref1	Ref2	Result
16		L	7	7
17		VL	3	4
Sheet

 

[kbetteley]

Thanks.

SorteI had a 4 by 3 range which I was using to find an exact match.

A much easier way is just to use Match, with the parameter set to 1 and only put the lowest value of each range in, i.e. 1,4,6,8. Then it doesn't get confused and returns the correct column.
Which I think is what you were suggesting whilst I was happily tying myself in knots...

K

 

[prabby25101981]

Kewl ! Thats easier, isn't it !