hlookup / index / match picking range that number occurs in

kbetteley

Board Regular
Joined
Jan 10, 2005
Messages
53
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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

prabby25101981

Active Member
Joined
Jul 28, 2010
Messages
348
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

Board Regular
Joined
Jan 10, 2005
Messages
53
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

Active Member
Joined
Jul 28, 2010
Messages
348
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

Board Regular
Joined
Jan 10, 2005
Messages
53

ADVERTISEMENT

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

Active Member
Joined
Jul 28, 2010
Messages
348
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

prabby, you were right first time, the address function is the way to go.
Excel Workbook
ABCDEF
61468
72579
8310
9VL46810
10L3579
11U2568
12VU1246
13
14
15Ref1Ref2Result
16L77
17VL34
Sheet
 

kbetteley

Board Regular
Joined
Jan 10, 2005
Messages
53
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,837
Messages
5,833,915
Members
430,244
Latest member
Ireland1

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