VLOOKUP and OFFSET

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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%
11KCAUCitadel101,219101,21950%
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

Thanks in advance,

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

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Perhaps you should use a If/Match() formula,

e.g.

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

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

seenfresh

Well-known Member
Joined
Jul 12, 2006
Messages
1,141
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
 

Forum statistics

Threads
1,141,679
Messages
5,707,786
Members
421,527
Latest member
Tamiwsw

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top