+ Reply to Thread
Results 1 to 12 of 12

If function to look up from a table on another sheet to return value from table

  1. #1
    Registered User
    Join Date
    10-29-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    9

    If function to look up from a table on another sheet to return value from table

    Hi,

    I have an issue I can't resolve and wondered if it is possible...

    I have a table on sheet 2

    sheet 2.jpg

    and I have another table on sheet 1

    sheet 1.jpg

    When I put a dept in sheet 2 in (column B) I want it to automatically look for that department in the table in sheet 1 and then return the value of which team (A or B) it responds to in to column C...

    Any ideas how I might achieve this please as I'm a bit of a novice and unsure?

    Many thanks

  2. #2
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: If function to look up from a table on another sheet to return value from table

    It's not the most sophisticated, but adding a helper column (highlighted in green) will allow you to autopopulate the team names (highlighted in blue). You can hide the helper column.
    Attached Files Attached Files

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,598

    Re: If function to look up from a table on another sheet to return value from table

    Put this formula in C2 of Sheet 2:

    =IF(ISNUMBER(MATCH(B2,'Sheet 1'!B$1:$D$1,0)),'Sheet 1'!$A$1,'Sheet 1'!$A$2)

    Then copy down.

    Hope this helps.

    Pete

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: If function to look up from a table on another sheet to return value from table

    =LOOKUP(2,1/(((Sheet1!$B$1:$B$2=b2)+(Sheet1!$C$1:$C$2=b2)+Sheet1!($D$1:$D$2=b2))>0),Sheet1!$A$1:$A$2)

    and copy down
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  5. #5
    Registered User
    Join Date
    10-29-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    9

    Re: If function to look up from a table on another sheet to return value from table

    Many thanks all for your help. first response works perfect as do the others.

  6. #6
    Registered User
    Join Date
    10-29-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    9

    Re: If function to look up from a table on another sheet to return value from table

    Another question however, If i end up having more teams (i.e 6 in total) it won't let me nest anymore if functions?

  7. #7
    Registered User
    Join Date
    10-29-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    41

    Re: If function to look up from a table on another sheet to return value from table

    This should solve it:
    Please Login or Register  to view this content.
    Put the code in C2 of Sheet 2. It will work for unlimited teams. If you want to add more departments per team, you can simply expand B:D to B:E, B:F, etc.

  8. #8
    Registered User
    Join Date
    10-29-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    9

    Re: If function to look up from a table on another sheet to return value from table

    That doesn't seem to work unless I'm doing something wrong but if I add that it didn't work...

  9. #9
    Registered User
    Join Date
    10-29-2014
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    41

    Re: If function to look up from a table on another sheet to return value from table

    Perhaps your sheet names are slightly different than the ones I used in the formula? In the two solutions posted before me, one used 'Sheet1' and another used 'Sheet 1'. Check if your file has sheet names with a space or without.

  10. #10
    Registered User
    Join Date
    10-29-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    9

    Re: If function to look up from a table on another sheet to return value from table

    Quote Originally Posted by hoyasaxa215 View Post
    It's not the most sophisticated, but adding a helper column (highlighted in green) will allow you to autopopulate the team names (highlighted in blue). You can hide the helper column.
    Hi Hoyasaxa215...Your lookup function worked perfect for me...Can you help with something else though...If I wanted to add a sub department in between column B & C so that it can look for the first department but then a second to determine what team it should be, how can I do that? I'm sure it is possible I just can't work it out!

    Thanks

  11. #11
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: If function to look up from a table on another sheet to return value from table

    Can you upload another sample file showing what you're trying to accomplish? I don't quite understand.

  12. #12
    Registered User
    Join Date
    10-29-2014
    Location
    England
    MS-Off Ver
    2010
    Posts
    9

    Re: If function to look up from a table on another sheet to return value from table

    Quote Originally Posted by hoyasaxa215 View Post
    Can you upload another sample file showing what you're trying to accomplish? I don't quite understand.
    sheet 2.jpg

    I want "Team" Column to initially search in dept before it returns the team value from sheet 1, but if there is something in Sub dept that will then take priority and then search in sheet 1.. Apologies I don't think I explained it very well before.

    sheet 1.jpg

    I wondered if there would need to be some sort of false statement to the formula in the lookup sheet you did?
    Last edited by Rucille; 10-30-2014 at 05:09 PM.

+ 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. LOOKUP TABLE -blank cell in the table array to return a figure
    By jonnops in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2014, 09:08 AM
  2. Replies: 4
    Last Post: 10-05-2012, 03:06 PM
  3. How do you return table values to another sheet?
    By chizz0320 in forum Excel General
    Replies: 3
    Last Post: 10-31-2011, 06:17 PM
  4. [SOLVED] Function that return a a vector or a table
    By Jean-Pierre Bidon in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-07-2005, 01:20 PM
  5. [SOLVED] Look up function-How can I return a value in a table
    By Stacy Haskins in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-13-2005, 03:06 PM

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