+ Reply to Thread
Results 1 to 7 of 7

Return variable column results depending on entries in multiple columns

  1. #1
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Return variable column results depending on entries in multiple columns

    Hi everyone,

    I've got a problem which I'm getting myself tied in knots with . I have a list of names and phone numbers on one sheet. On multiple other sheets, people will be putting in names, which then need to have the correct numbers inserted beside them. So far, so simple (Index/Match).
    Here's the problem: the names and numbers are in cols A and B but in cols C and D are updates to the names and numbers. Sometimes the old name is still valid, with a new number; sometimes there's a new name but the old number is still valid; sometimes there's a new name and new number. The entries on the multiple sheets could be either the old or new names and I need to extract the correct number - either old or new as appropriate.
    I could just put all the old names and old numbers into the new name/number columns and do a simple Index/Match that way, but then the changes tend to get lost (there's a LOT of names/numbers).

    Here's what the name/number data looks like:
    .....A........B........C.........D
    1..OldName...OldNo...NewName...NewNo
    2...aaa.......111
    3...bbb.......222....bbbnew
    4...ccc.......333.............333000
    5...ddd.......444....dddnew...444000

    Here's what I want:
    .....F.........G
    1..Name.......No
    2..aaa.........111
    3..bbbnew......222
    4..ccc......333000
    5..dddnew...444000

    I've been trying various combinations of IFERROR, OR, ISERROR, some=0, etc, etc - but I can't get a formula which works for all combinations of OldName+OldNo, OldName+NewNo, NewName+OldNo and NewName+NewNo.

    For example, this formula works for new numbers, but not with old numbers:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It returns:
    0
    0
    333000
    444000


    Attached is an example file. It has named ranges for OldName, OldNo, NewName, NewNo, to make things a bit easier. If someone here can get his/her head round this and let me know what I'm missing, I'd greatly appreciate it.


    Edit: Also, I can't change the structure of the multiple sheets where I need the correct numbers, nor can I easily add any other sheets (the file is used as a source to generate a telephone directory on another system which is very particular about what's there).
    Last edited by Aardigspook; 07-20-2016 at 05:34 AM. Reason: Add note about file structure
    Regards,
    Aardigspook

    I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Return variable column results depending on entries in multiple columns

    You can use a simple formula like
    Please Login or Register  to view this content.
    for name, drag it to the right one column for No.
    Basically, if there's new value, use it, otherwise use the old one.

    Here is your file with sample formula.

    Was this what you were looking for?
    Last edited by Lemice; 07-20-2016 at 06:08 AM.
    (copy pasta from Ford)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools

    Regards,
    Lem

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Return variable column results depending on entries in multiple columns

    edited reply:- misread question

    This should do it.

    =IFERROR(LOOKUP(1E+100,INDEX(OldNo:NewNo,IFERROR(MATCH(F2,NewName,0),MATCH(F2,OldName,0)),0)),"")
    Last edited by jason.b75; 07-20-2016 at 06:26 AM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: Return variable column results depending on entries in multiple columns

    If there are only columns A to D

    =IF(C2<>"",C2,A2)

    and

    =IF(D2<>"",D2,B2)

  5. #5
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Return variable column results depending on entries in multiple columns

    Sorry - I realise now I didn't explain myself very well .

    The names won't necessarily be entered in order, there will be gaps, and not all numbers on the list will be used in each section.
    Instead of the example spreadsheet, which I obviously over-simplified, I've attached a stripped-down version of the actual file.

    The list of names and numbers is on the 'Full list of numbers' sheet. The formula is needed on the 'DataEntry' sheet. I need it in column F, to find the entry in column E, look it up in either column A or C on the 'Full list of numbers' sheet and return a result from either column B or D on that sheet. Column J is there to show what the results should be - it's not there in the actual file (which has a lot more names/numbers, hundreds of lines on each DataEntry-type sheet and dozens of those sheets - but they all need to refer to the full name/number list.

    Hope that helps explain what I need - sorry for the unclear explanation previously.

  6. #6
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Return variable column results depending on entries in multiple columns

    Did you try the formula in post #3?

  7. #7
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,404

    Re: Return variable column results depending on entries in multiple columns

    I did, but it returned zeroes, so I thought maybe my data was over-simplified. Having looked at it again, for some reason the defined Names had changed their references - A$2 had become IV$2, for example. I fixed the ranges and your formula worked - many thanks!
    Last edited by Aardigspook; 07-20-2016 at 10:44 AM. Reason: fix typo

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 8
    Last Post: 06-13-2015, 05:00 PM
  2. [SOLVED] Name Variable LOOKUP to return multiple results
    By mmayna03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-03-2012, 01:59 PM
  3. Replies: 0
    Last Post: 08-14-2012, 01:18 PM
  4. [SOLVED] return multiple results from one column based on cells in another column
    By chollyfunk in forum Excel General
    Replies: 5
    Last Post: 05-09-2012, 05:20 AM
  5. Replies: 4
    Last Post: 01-14-2011, 09:30 PM
  6. Vlookup data and return results on multiple entries
    By mikeydaman in forum Excel General
    Replies: 18
    Last Post: 08-18-2010, 08:22 AM
  7. Enter variable, return results from several columns
    By swirelessnw in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-16-2008, 07:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1