Multiple Vlookup ranges/table arrays

[JC Preston]

I am trying return a value that looks up over 5 ranges and not having much joy. I want to lookup Cell A1 in the second colum of ranges B1:C30, D1:E30, F1:G30, H1:I30 & J1:K30.

So i need to combine the following into one formula:

=vlookup(A1,B1:C30,2,false)
=vlookup(A1,D1:E30,2,false)
=vlookup(A1,F1:G30,2,false)
=vlookup(A1,H1:I30,2,false)
=vlookup(A1,J1:K30,2,false)

Would then like to copy down to A2, A3, A4 etc - Is anyone able to help???

 

[Dannyh1]

Hi Try,

=OFFSET(INDEX(B1:K30,MATCH(A1,B1:K30,0)),0,2)

*IGNORE SORRY

 

[shg]

So i need to combine the following into one formula:

"Combine" in what sense?

 

[Dannyh1]

There definatley must be a better way, but I can't think at the moment. This is a horrible solution but should work:

=sumif(B:B,A1,C:C)+sumif(D:D,A1,E:E)+sumif(F:F,A1,G:G)+sumif(H:H,A1,I:I)+sumif(J:J,A1,K:K)

 

[JC Preston]

<TABLE style="WIDTH: 490pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=649 border=0><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 37pt; mso-width-source: userset; mso-width-alt: 1792" width=49><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 id=td_post_2717898 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 60pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=80 height=20>AB12 3LN</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>4</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78>E6 6DP</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 65pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=86>LS9 0EF</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>3</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=78>PE28 4DZ</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 62pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=82>SO30 0FE</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AB12 4BA</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">E6 6DR</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>3</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LS9 0EG</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>3</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PE28 4ER</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SO30 0FJ</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AB12 4BB</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>4</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">E6 6DS</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LS9 0EH</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>3</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PE28 4ET</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SO30 0GA</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=49></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AB12 4BD</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">E6 6DT</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LS9 0EL</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=49></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PE28 4FJ</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=49></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SO30 0GG</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>5</TD></TR>Basically im looking up postcodes with ratings next to them so need to to return the value next to the corresponding postcode. If cell A1 appears in the first section starting AB12 need to return the value next to it ie "4", if its not then check the next section starting E6 etc. etc.



<TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 id=td_post_2717898 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AB12 4BE</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=49></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">E6 6DU</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>5</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LS9 0EN</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>3</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PE28 4JA</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SO30 0GJ</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>5</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>AB12 4BG</TD><TD class=xl67 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">E6 6DX</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>2</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">LS9 0ER</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>3</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">PE28 4NQ</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=49></TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">SO30 0GL</TD><TD class=xl66 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver 0.5pt solid; WIDTH: 37pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=49>5</TD></TR></TBODY></TABLE>

 

[Krishnakumar]

HI,

May be..

=LOOKUP(9.999E+307,CHOOSE({1,2,3,4,5},VLOOKUP(A1,B1:C6,2,0),VLOOKUP(A1,D1:E6,2,0),VLOOKUP(A1,F1:G6,2,0),VLOOKUP(A1,H1:I6,2,0),VLOOKUP(A1,J1:K6,2,0)))

 

[JC Preston]

Kris, looks to have done the trick thanks for your help!!

Thanks to all the other suggestions also, much appreciated!!