Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to automatically number an index column | Excel Worksheet Functions | |||
Multiple vlookup arguments | Excel Worksheet Functions | |||
vlookup with more than number to be retrieved | Excel Worksheet Functions | |||
vlookup for multiple columns | Excel Worksheet Functions | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions |