+ Reply to Thread
Results 1 to 7 of 7

If Vlook Up

  1. #1
    Forum Contributor
    Join Date
    09-20-2018
    Location
    Jakarta
    MS-Off Ver
    google
    Posts
    102

    If Vlook Up

    Hi,

    May I know if anyone knows the correct formula for colomn G in sheet 1 in this google sheet please ? :
    https://docs.google.com/spreadsheets...jEs/edit#gid=0

    So I need to crawl the 'names' and 'Screenshot of Attempt' from sheet 2 & sheet 3 in colomn B & colomn E, which referrerd to the colomn name in sheet 1 colomn B. If it does existed in sheet 2 and/or sheet 3 colomn B & colomn E, then in sheet 1 colomn G should be specified as 'sent'.


    Thank You !
    Last edited by maniacs205; 03-31-2023 at 02:23 PM.

  2. #2
    Forum Contributor
    Join Date
    09-20-2018
    Location
    Jakarta
    MS-Off Ver
    google
    Posts
    102

    Re: If Vlook Up

    I found an answer in chatGPT, but wrong....
    https://prnt.sc/h4RtHn_EZ0qU

  3. #3
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: If Vlook Up

    I put the following Arrayformula in cell G2 of sheet1 so you won't have to drag the formula down for each entry in column B:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Hope this gives you what you're after.

  4. #4
    Forum Contributor
    Join Date
    09-20-2018
    Location
    Jakarta
    MS-Off Ver
    google
    Posts
    102

    Re: If Vlook Up

    I am still looking at this, but actually, in our real sheet there are more than just sheet2 and sheet3. Would you please tell me how the formula would be like if there was more than just sheet2 and sheet3 ?

    Thank you

  5. #5
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: If Vlook Up

    If you put the following formula in cell G2 it will do what you want providing each sheet has the same layout, but how many sheets do you actually have?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Can you share your spreadsheet to give us a better visualization of what you're trying to accomplish?

  6. #6
    Forum Contributor
    Join Date
    09-20-2018
    Location
    Jakarta
    MS-Off Ver
    google
    Posts
    102

    Re: If Vlook Up

    Let us just continue using the current Gsheet https://docs.google.com/spreadsheets...jEs/edit#gid=0

    There are few problems here actually :
    1. The subsheet's name have space -> so I have changed the name accordingly, my bad..
    2. Does array formula means click ctrl + shift + enter ? I can't do it
    3. Your first formula is different than the first one, because i may need to add more and more type of sheet 1 and type of sheet 2 - sheet 5

    =ArrayFormula(IF(LEN(B2:B)=0,"",IF(COUNTIF(Sheet2!B2:B,Sheet1!B2:B)+COUNTIF(Sheet3!B2:B,Sheet1!B2:B)>0,"Sent","No")))

    =ArrayFormula(IF(LEN(B2:B)=0,"",IF(COUNTIF({Sheet2!B2:B;Sheet3!B2:B;Sheet4!B2:B;Sheet5!B2:B},Sheet1!B2:B)>0,"Sent","Not Sent")))

    Sorry I am very confused

  7. #7
    Forum Contributor
    Join Date
    12-17-2013
    Location
    ON, Canada
    MS-Off Ver
    MS 365
    Posts
    171

    Re: If Vlook Up

    The Arrayformula in cell G2 is doing what you showed in your expected results. My second formula differed from the first because you asked how it would look like if there were additional sheets added. The formula is only entered in cell G2 and does the calculations for all of that column.

    I'm sorry but I'm not seeing a clear picture of what you're trying to accomplish. Perhaps someone else can assist you.

+ 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. [SOLVED] VLOOK Formula Only Shows the VLOOK Formula Instead of Lookup Value
    By onlyonekj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-29-2017, 03:30 PM
  2. [SOLVED] Vlook Up with >= <= <
    By vajeednawab in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-05-2013, 08:02 AM
  3. sum if and Vlook up
    By fletchk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-13-2013, 05:28 PM
  4. [SOLVED] In vlook if value comes to #n/a i want there 0
    By satputenandkumar0 in forum Excel General
    Replies: 10
    Last Post: 11-24-2012, 05:36 AM
  5. VLook Up Help
    By Merlin54k in forum Excel General
    Replies: 4
    Last Post: 06-18-2008, 11:35 AM
  6. Help fix my Vlook please
    By Mycotopian in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2007, 04:59 AM
  7. vlook
    By dj_siek in forum Excel General
    Replies: 3
    Last Post: 04-10-2007, 12:44 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