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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

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
10,693
Office Version
2019
Platform
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,722
Messages
5,513,030
Members
408,933
Latest member
frossen

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top