If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding a value or the one next to it in a range NOT array
Picture data in a phone book with 5 columns of names and numbers 10 cols
total. I need a function that will look up the name and return the number that will work if I don't know in advance which column the name will be in. (they aren't in alpha order). Vlookup and match presume I know which column I am dealing with. Help this has taken many unprofitable hours from my life. |
#2
|
|||
|
|||
Finding a value or the one next to it in a range NOT array
So, do you mean you have names in columns A, C, E, G and I and numbers
in columns B, D, F, H and J? The name you are looking for could be anywhere in those 5 columns (or maybe not present at all)? Pete On Aug 6, 8:21*am, advanced novice advanced wrote: Picture data in a phone book with 5 columns of names and numbers 10 cols total. I need a function that will look up the name and return the number that will work if I don't know in advance which column the name will be in. |
#3
|
|||
|
|||
Finding a value or the one next to it in a range NOT array
Exactly, except in my case I know the name will always be there, but it is
possible the number will have a value of 0. Thank you for attemping to answer this! "Pete_UK" wrote: So, do you mean you have names in columns A, C, E, G and I and numbers in columns B, D, F, H and J? The name you are looking for could be anywhere in those 5 columns (or maybe not present at all)? Pete On Aug 6, 8:21 am, advanced novice advanced wrote: Picture data in a phone book with 5 columns of names and numbers 10 cols total. I need a function that will look up the name and return the number that will work if I don't know in advance which column the name will be in. (they aren't in alpha order). Vlookup and match presume I know which column I am dealing with. Help this has taken many unprofitable hours from my life.. |
#4
|
|||
|
|||
Finding a value or the one next to it in a range NOT array
Okay, I'm assuming you are entering a name into cell A1 on Sheet2 and
you want your answer returned to cell B1, and that your data is on Sheet1. Put this formula in B1: =IF(ISNA(MATCH(A1,Sheet1!A:A,0)),"",VLOOKUP(A1,She et1!A:B, 2,0))&IF(ISNA(MATCH(A1,Sheet1!C:C,0)),"",VLOOKUP(A 1,Sheet1!C, 2,0))&IF(ISNA(MATCH(A1,Sheet1!E:E,0)),"",VLOOKUP(A 1,Sheet1!E:F, 2,0))&IF(ISNA(MATCH(A1,Sheet1!G:G,0)),"",VLOOKUP(A 1,Sheet1!G:H, 2,0))&IF(ISNA(MATCH(A1,Sheet1!I:I,0)),"",VLOOKUP(A 1,Sheet1!I:J,2,0)) Hope this helps. Pete On Aug 6, 2:38*pm, advanced novice wrote: Exactly, except in my case I know the name will always be there, but it is possible the number will have a value of 0. Thank you for attemping to answer this! "Pete_UK" wrote: So, do you mean you have names in columns A, C, E, G and I and numbers in columns B, D, F, H and J? The name you are looking for could be anywhere in those 5 columns (or maybe not present at all)? Pete On Aug 6, 8:21 am, advanced novice advanced wrote: Picture data in a phone book with 5 columns of names and numbers 10 cols total. I need a function that will look up the name and return the number that will work if I don't know in advance which column the name will be in. (they aren't in alpha order). Vlookup and match presume I know which column I am dealing with. Help this has taken many unprofitable hours from my life..- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|