Looking for VLOOKUP Trick

shdawson · Dec 29, 2010

Hi,

Love VLOOKUP. Has met a lot of needs I have had over the years.

Question:
What do you do when the lookup value is to the RIGHT of the lookup range? In the past, I have referenced the row via =B2, say in column Z. It works, but it can get messy.

Is there another trick out there to overcome this problem?

Thanks,
S

shdawson · Jan 5, 2011

schielrn,

Thanks for the post! This indeed a unique problem, but the answer will provide for a lot of other problems in life to be addressed as well.

You have blown me out of the water with your formula. Can you please walk me through it?

Thanks,
S

schielrn · Jan 5, 2011

shdawson said:
schielrn,

Thanks for the post! This indeed a unique problem, but the answer will provide for a lot of other problems in life to be addressed as well.

You have blown me out of the water with your formula. Can you please walk me through it?

Thanks,
S

This may be a little far-fetched, but actually might be of some use after looking at it, even though as stated index/match could achieve the same thing and you probably wouldn't want to do it with too many columns:

Excel Workbook

A

B

C

D

E

F

G

H

I

J

K

1

Monday

Tuesday

Wednesday

Thursday

Friday

Leader Type

Leader

Tuesday

4.5

2

1

1.5

2.25

3.38

5.07

Pre-rankings Leader

Reliable Leader

Friday

20.25

3

2

3

4.5

6.75

10.13

New Leader

Anchor

Wednesday

13.5

4

3

4.5

6.75

10.13

15.2

Leader

5

4

6

9

13.5

20.25

Reliable Leader

6

5

7.5

11.25

16.88

25.32

Team Leader

7

6

9

13.5

20.25

30.38

Anchor

Sheet1

Cell Formulas
Range	Formula
K1	=VLOOKUP(I1,CHOOSE({1,2,3,4,5,6},$F$2:$F$7,$A$2:$A$7,$B$2:$B$7,$C$2:$C$7,$D$2:$D$7,$E$2:$E$7),MATCH(J1,$A$1:$E$1,0)+1,0)

But basically it is using vlookup to look left and then using the match function to determine which column it needs to pull from and I am adding 1 to increase the value to match the numbers in teh choose function.

Hope that helps a little.

shdawson · Jan 5, 2011

WOW! That would probably melt my laptop through the crust of the Earth with it getting so hot! HA!

Let's see if we can stream line this code a bit. Perhaps tomorrow things will look simpler. Please let me know.

Thanks,
S

Haseeb Avarakkan · Jan 5, 2011

Another way,

<TABLE style="WIDTH: 668pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=890><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 20pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=26></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>

A