Multiple Column Index Number in VLookup

G

GorillaBoze

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??
 
S

swatsp0p

VLOOKUP, by default, returns a value from the same row in which th
'target' value is found. You don't tell us what you want to return I
your value is found within the three column range. Do you simply wan
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
 
S

swatsp0p

GorillaBoze said:
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. Mayb
something else will work better??
Click to expand...

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

=IF(ISNA(A254,Sheet1!$N$4:$P$469,3,0)),VLOOKUP(A254,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 foun
in N it will return the value in P. If found in O, it returns value i
P. If not found at all, returns #N/A.

Hope this works for you
 
G

GorillaBoze

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?
 
G

GorillaBoze

Okay, using the ISNA before VLookup works for 2 columns. How do I add
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)),(VLOOKUP(A254,Sheet1!$N$4:$N$1000,1,FALSE)))

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

Sugestions?
 
A

Ashish Mathur

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
 
S

swatsp0p

GorillaBoze said:
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)),(VLOOKUP(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??
Click to expand...

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
 
S

swatsp0p

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

Cheers!
 
Top