Multiple Column Index Number in VLookup
 

Is it possible for a VLookup to look in 3 columns instead of 1?
For example: =VLOOKUP(A254,Sheet1!$N$4:$O$469,*1:3*,FALSE)

There is only data in one of the three columns in each row. Maybe
something else will work better??


--
GorillaBoze
------------------------------------------------------------------------
GorillaBoze's Profile: http://www.excelforum.com/member.php...fo&userid=5165
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

VLOOKUP, by default, returns a value from the same row in which the
'target' value is found. You don't tell us what you want to return IF
your value is found within the three column range. Do you simply want
to know if the contents of A254 is found within your lookup range?

Also, your given range: Sheet1!$N$4:$O$469 is only two columns wide.

More info, please.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

GorillaBoze Wrote:
Is it possible for a VLookup to look in 3 columns instead of 1?
For example: =VLOOKUP(A254,Sheet1!$N$4:$O$469,*1:3*,FALSE)

There is only data in one of the three columns in each row. Maybe
something else will work better??

As an addendum, if we assume you want to look in N and O for your value
and return from P if found, try this:

=IF(ISNA(A254,Sheet1!$N$4:$P$469,3,0)),VLOOKUP(A25 4,Sheet1!$O$4:$P$469,2,0),VLOOKUP(A254,Sheet1!$N$4 :$P$469,3,0))

If your value is not in N (returns #N/A), it will look in O. If found
in N it will return the value in P. If found in O, it returns value in
P. If not found at all, returns #N/A.

Hope this works for you.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

Sorry for the column error.

On Sheet1 I have:

_Column_A_ - Mutual Fund Name
_Column_N_ - Ticker1
_Column_O_ - Ticker2
_Column_P_ - Ticker3

-Some of the Mutual Funds have 1 ticker, some have 2, and some have 3.
-


On Sheet2 I have a list of our current holdings.

_Column_A_ - Mutual Fund Name
_Column_B_ - Ticker
_Column_C_ - Value

In Column D on sheet2 I would like to see if the ticker in Column B is
in any of the 3 columns in Sheet1.

=VLOOKUP(B2,Sheet1!$N$1:$P$500,1:3,FALSE)

I know the above does not work, but is there a way to look in 3 rows of
data for 1 ticker?


--
GorillaBoze
------------------------------------------------------------------------
GorillaBoze's Profile: http://www.excelforum.com/member.php...fo&userid=5165
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

Okay, using the ISNA before VLookup works for 2 columns. How do I add a
3rd?

Here is what I have:
=IF(ISNA(VLOOKUP(A254,Sheet1!$N$4:$N$1000,1,FALSE) ),(VLOOKUP(A254,Sheet1!$O$4:$O$1000,1,FALSE)),(VLO OKUP(A254,Sheet1!$N$4:$N$1000,1,FALSE)))

If it is not in column N, look in column O. I need it to look in
Column P if it is not in column N or O.

Sugestions??


--
GorillaBoze
------------------------------------------------------------------------
GorillaBoze's Profile: http://www.excelforum.com/member.php...fo&userid=5165
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

Hi,

You may try the following array formula (Ctrl+Shift+Enter)

=or(exact(B1,N1:N50))

B1 is on sheet 2, N1:N50 is on sheet 1

The formula will return True if the ticker is found.

Regards,

Ashish Mathur

"GorillaBoze" wrote:


Sorry for the column error.

On Sheet1 I have:

_Column_A_ - Mutual Fund Name
_Column_N_ - Ticker1
_Column_O_ - Ticker2
_Column_P_ - Ticker3

-Some of the Mutual Funds have 1 ticker, some have 2, and some have 3.
-


On Sheet2 I have a list of our current holdings.

_Column_A_ - Mutual Fund Name
_Column_B_ - Ticker
_Column_C_ - Value

In Column D on sheet2 I would like to see if the ticker in Column B is
in any of the 3 columns in Sheet1.

=VLOOKUP(B2,Sheet1!$N$1:$P$500,1:3,FALSE)

I know the above does not work, but is there a way to look in 3 rows of
data for 1 ticker?


--
GorillaBoze
------------------------------------------------------------------------
GorillaBoze's Profile: http://www.excelforum.com/member.php...fo&userid=5165
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

GorillaBoze Wrote:
Okay, using the ISNA before VLookup works for 2 columns. How do I add a
3rd?

Here is what I have:
=IF(ISNA(VLOOKUP(A254,Sheet1!$N$4:$N$1000,1,FALSE) ),(VLOOKUP(A254,Sheet1!$O$4:$O$1000,1,FALSE)),(VLO OKUP(A254,Sheet1!$N$4:$N$1000,1,FALSE)))

If it is not in column N, look in column O. I need it to look in
Column P if it is not in column N or O.

Sugestions??

To look at three columns and return the Ticker where found (or Not
Found if not there) try:

=IF(NOT(ISNA(VLOOKUP(A254,Sheet1!$N$4:$N$1000,1,0) )),"TICKER1",IF(NOT(ISNA(VLOOKUP(A254,Sheet1!$O$4: $O$1000,1,0))),"TICKER2",IF(NOT(ISNA(VLOOKUP(A254, Sheet1!$P$4:$P$1000,1,0))),"TICKER3","NOT
FOUND")))

Good Luck


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

That worked great Bruce.

Thank You

-Chad


--
GorillaBoze
------------------------------------------------------------------------
GorillaBoze's Profile: http://www.excelforum.com/member.php...fo&userid=5165
View this thread: http://www.excelforum.com/showthread...hreadid=479919

Multiple Column Index Number in VLookup
 

I'm glad it worked for you. Thanks for the feedback, it is always
appreciated.

Cheers!


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=479919