+ Reply to Thread
Results 1 to 7 of 7

Searching for value in different columns. and returning different info based on the column

  1. #1
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Searching for value in different columns. and returning different info based on the column

    So basically I have a list of zip codes, and i need to setup a way for a zip code to be checked against 4 lists, and if it appears in the list, I need it to return a certain message based on the list that it appears in.

    Attached is a sample file to show some of the sample information. The zip code that needs to be checked is column B on Sheet 2. (Dest Zip).

    So for example, i need to look for the value of b5 and run it across tab DAS columns B D F and H. IF B5 appears in one of those columns, then I need a message returned on tab Sheet 2, that says the name of the column.

    Eventually, based on the column, and other shipping options, a surcharge will be added to the numbers in Column D to then return a new price.
    Attached Files Attached Files

  2. #2
    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,007

    Re: Searching for value in different columns. and returning different info based on the co

    Try (found after Google search) ... in F5

    Enter with CTrl+Shift+Enter and copy down

    =IFERROR(INDEX(DAS!$D$1:$H$1,SMALL(IF(DAS!$D$2:$H$20000=$B5,COLUMN(DAS!$D$2:$H$20000)-COLUMN(DAS!$D$2)+1),1)),"")

    returns column header

  3. #3
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: Searching for value in different columns. and returning different info based on the co

    Thank you John!

    This is working almost perfectly.

    The bug that I am running into is on zip codes that start with a "0" now. if i set the info in D2:G10 to "zip code" so that 5 digits show for the zip, the proper message is not returned on C5

    I tried changing d2:G10 to Text instead of number, and that also does not work.

    Do you have any thoughts on how to get around this???

  4. #4
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: Searching for value in different columns. and returning different info based on the co

    The other thing problem that I just noticed is when the value in column B is empty, it is not showing a "0" or a blank, it is instead pulling the value in D1

  5. #5
    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,007

    Re: Searching for value in different columns. and returning different info based on the co

    Original formula was incorrect:

    =IFERROR(INDEX(DAS!$B$1:$H$1,SMALL(IF(DAS!$B$2:$H$20000=$B5,COLUMN(DAS!$B$2:$H$20000)-COLUMN(DAS!$B$2)+1),1)),"")

    Convert to text by using this in a spare column:

    =TEXT(B8,"00000") and copy down as required.

    Then Copy and Paste Special Values into original column (B in the example)

    This worked OK for me.

    Note I changed a some of the data to test it out.

    I'll call it a day (or night) now.
    Attached Files Attached Files

  6. #6
    Spammer
    Join Date
    11-21-2014
    Location
    California
    MS-Off Ver
    2010
    Posts
    387

    Re: Searching for value in different columns. and returning different info based on the co

    I tried doing this, and was not able to get it to work.

    Above some of the "B"s are showing as bold. Is there supposed to be something different going on with those?

    Also, i cannot turn the reference zip code into Text, or it will break other calculations that are going on. Are there other solutions?

  7. #7
    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,007

    Re: Searching for value in different columns. and returning different info based on the co

    I reformatted ALL the cells as Zip Code including those in Sheet2 and it worked!

    See attached.
    Attached Files Attached Files
    Last edited by JohnTopley; 08-20-2015 at 03:41 AM.

+ 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: 10
    Last Post: 01-26-2015, 11:03 AM
  2. Replies: 2
    Last Post: 12-18-2014, 01:56 PM
  3. [SOLVED] Returning value from one column based on two neighboring columns
    By Ezzard in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-26-2014, 11:50 AM
  4. [SOLVED] Searching data in two columns and returning data from a third column
    By d dubya in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-12-2014, 05:02 PM
  5. [SOLVED] Returning value from a third column; based on two other columns.
    By tony8980 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-25-2012, 12:32 AM
  6. Replies: 5
    Last Post: 05-04-2011, 06:51 PM
  7. Searching Columns and Returning Row values
    By jasteinh in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-31-2010, 09:09 AM

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