# VLOOKUP and OFFSET

#### seenfresh

##### Well-known Member
I am trying to perform a VLOOKUP from one ws to another for the value in column A

Thus, VLOOKUP(A3,'Life Input'!\$A:\$A,INDEX(\$C3,0))

Here is a visual:
National Revenue Allocation.xls
ABCDEF
3KGBTBarrington727,988#REF!50%
4KTVOBarrington307,672307,67250%
5KXTU/KXRMBarrington29,88329,88350%
6WACHBarrington54,22154,22150%
7WLUCBarrington485,912485,91250%
8WNWOBarrington288,808288,80850%
9WPBNBarrington204,169204,16950%
10WSTMBarrington494,209494,20950%
Lifestyles

I am trying to enter a formula the if the VLOOKUP is True to return the value from Column C thus, Offseting the reference (D3,0,-1)

The VLOOKUP formula is fine I am just having trouble with structuring multiple agruments. I believe OFFSET is the right argument if I want to return the value if true from D3 0rows and -1column, but my formula returns a REF! error

I would appreciated any advice possible

Sean

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Is it?

VLOOKUP(A3,'Life Input'!\$A:\$C,COLUMNS('Life Input'!\$A:\$C))

Perhaps you should use a If/Match() formula,

e.g.

=If(isnumber(Match(A3,'Life Input'!\$A:\$A,0)),\$C3,"")

Thank you guys! this formula worked great!

=If(isnumber(Match(A3,'Life Input'!\$A:\$A,0)),\$C3,"")

I see here, I forgot I could use a Match function to exploit the True case and the reference the value index from the C column using match type 0.

This formula

VLOOKUP(A3,'Life Input'!\$A:\$C,COLUMNS('Life Input'!\$A:\$C))

Has the same Idea except I don't think I was clear enough with my explanation earlier sorry Andrew! This return the value from the ws that I am performing the VLOOKUP into thus Life Input! instead of the current ws National Revenue Allocation!

In addition one thing I don't understand with this above formula is COLUMNS('Life Input'!\$A:\$C) portion of it I understand this is the index number/range how does it determin which column to return the result from when it has \$A:\$C?

Thanks again gentleman much appreciated!

Sean

The COLUMNS function returns the number of columns in a range. So:

COLUMNS('Life Input'!\$A:\$C)

returns 3. If you use the hard number 3 as the column_index_number it won't adjust if you insert columns between A:C.

Nice... I had no clue about that possibility. Thats a good idea because it decreases the amount of checks and balances that I would have to do ever time I make adjustments to the Life Input ws. Thanks Andrew!

Take Care,

Sean

Replies
6
Views
552
Replies
2
Views
69
Replies
2
Views
284
Replies
2
Views
147
Replies
10
Views
426

1,217,356
Messages
6,136,078
Members
449,988
Latest member
Mabbas

### 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.

### Which adblocker are you using?

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

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