Multi Colum Named Range

[Snowshoeken]

I am fairly certain there is no way to get this to work, and I have a work around, but. I was hoping someone could tell me why this doesn't work, and maybe a way to make it work.

I was trying to use a vlookup referencing a named range that uses multiple columns;

=$F$6:$G$13,$H$6:$I$13 - Note the ","

So I am thinking theoretically it should work, but no dice. Thoughts? Thank you.

 

[JackDanIce]

I'm guessing because VLOOKUP requires all your look up values to be in the first column of the data range, named or otherwise. It doesn't recognise that your data continues after the "," as the same first column

 

[Jonmo1]

It just won't work that way...why I'm not sure.

But why not just use =$F$6:$I$13 ?
I presume that was a simplified example of something more complicated..


Vlookup is really not the best option anyway.
If your goal was to match a value in column F and return the corresponding value from column I, use Index/Match instead..

=INDEX($I$6:$I$13,MATCH(somevalue,$F$6:$F$13,0))


Hope that helps.

 

[number kruncher]

You could nest the VLOOKUPS, within an IF statement, using Match to test for occurence in seperate columns.

 

[Snowshoeken]

The work around was the nested if statement.

The reason for not extending the named range vertically was to fit on the page and not have to move other formula results.

Thanks for the responces!!!

 

[Jonmo1]

OH, ok. Misunderstood...

So you want to turn

ABCD
EFGH

Into

AB
EF
CD
GH

 

[Snowshoeken]

Table looked like below. The vlookup was performed using a =RANDBETWEEN(1,16) as the match. The range was =A1:B8,C1:D8

A B C D
1 1 Data 9 Data
2 2 Data 10 Data
3 3 Data 11 Data
4 4 Data 12 Data
5 5 Data 13 Data
6 6 Data 14 Data
7 7 Data 15 Data
8 8 Data 16 Data

 

[T. Valko]

Table looked like below. The vlookup was performed using a =RANDBETWEEN(1,16) as the match. The range was =A1:B8,C1:D8

A B C D
1 1 Data 9 Data
2 2 Data 10 Data
3 3 Data 11 Data
4 4 Data 12 Data
5 5 Data 13 Data
6 6 Data 14 Data
7 7 Data 15 Data
8 8 Data 16 Data

Put the RANDBETWEEN formula in a cell, say F1.

Then:

=VLOOKUP(F1,IF(F1>8,C1:D8,A1:B8),2)

 

[Snowshoeken]

This was my work around. Random2 = 1 thru 8 and Random3 = 9 thru 16

=IF(F1<9,VLOOKUP(F1,Random2,2,0),VLOOKUP(F1,Random3,2,0))

Thanks everybody!