VLOOKUP and OFFSET

[seenfresh]

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
	A	B	C	D	E	F
3	KGBT	Barrington	727,988	#REF!		50%
4	KTVO	Barrington	307,672	307,672		50%
5	KXTU/KXRM	Barrington	29,883	29,883		50%
6	WACH	Barrington	54,221	54,221		50%
7	WLUC	Barrington	485,912	485,912		50%
8	WNWO	Barrington	288,808	288,808		50%
9	WPBN	Barrington	204,169	204,169		50%
10	WSTM	Barrington	494,209	494,209		50%
11	KCAU	Citadel	101,219	101,219		50%
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

 

[Andrew Poulsom]

Is it?

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

 

[NBVC]

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

e.g.

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

 

[seenfresh]

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]

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]

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