Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Excel Table with IF & Vlookup function Formula Config

  1. #1

    Excel Table with IF & Vlookup function Formula Config



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear Friends,

    May I request for assistance please in figuring out the formula for the attached sheet?

    The logic I wish to incorporate are as follows;

    • IF B15 is = AA-AE, then product will be equal to $5 to 10 days premium…etc.
    • IF F12 is = USD then F15 F19 selection will be from AA to AE found under "PC Sheet"
    • IF C11=North America then F12= USD)


    Hope you can help me configure the attached worksheet, many thanks!

    Regards,

    Francis
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,415
    Articles
    0
    I am not sure if I understood, but try these to see if I did.

    In F12:

    =MID(TRIM(SUBSTITUTE(VLOOKUP(C11&"*",PC!$G$3:$G$20,1,0),C11,"")),2,3)

    in B15:

    =IFERROR(TRIM(INDEX(PC!$D$3:$D$114&" "&PC!$E$3:$E$114,MATCH("*"&$F$12&"*",PC!$A$3:$A$114,0)+ROWS($B$15:$B15))),"")

    copied down

    in D15:

    =IF(B15="","",INDEX(PC!$B$3:$B$114,MATCH("*"&$F$12&"*",PC!$A$3:$A$114,0)+ROWS($B$15:$B15)))

    copied down


  3. #3
    See if this is what you had in mind
    Attached Files Attached Files

  4. #4
    After i looked at your instructions again i think i missed something on the first file i uploaded. I think this revised file was more like what you showed in your instructions
    Attached Files Attached Files

  5. #5
    Quote Originally Posted by tommyt61 View Post
    After i looked at your instructions again i think i missed something on the first file i uploaded. I think this revised file was more like what you showed in your instructions
    Hi Tmmy,

    I just want to say thank you very much for the help you have extended to me, I was able to combain all that you sent and was able to create a working template.

    Veyr best regards,

    Francis

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,415
    Articles
    0
    You're welcome.


  7. #7

    Talking Solved-excel table with IF and VlookupFunction formula Config

    Quote Originally Posted by NBVC View Post
    You're welcome.
    NBC and tommy, thank you

  8. #8
    Sure thing ... glad you were able to find a way to make it work .

    Have a good one !

  9. #9

    Comparing long text file from one excel sheet to another

    Greetings from the Philippines!

    I am working for a news broadcast network in the Philippines. Currently, we are working on a story on the performance of our lawmakers by looking at the quality of bills they file in Congress.

    Our political system allows lawmakers to be reelected on three terms. According to experts, this political set up has allowed this phenomenon: the bills they filed from previous Congress are rehashed in the present Congress. However, empirical data are scarce, or worst non-existent. Experts base their opinion on anecdotal evidence.

    It is in this light that we would like to study this phenomenon. We would like to shed light on the magnitude of this issue by being the first network or agency that would embark on an empirical study.

    We have retrieved from our Congress’ database the list of bills our lawmakers filed in the 13th and 14th Congress. The bills are formatted in Excel format.

    Historically, more than half (of the 200 plus) lawmakers are reelected every Congress, and roughly, they principally author more than 30 bills every Congress. This presents a major obstacle in our study.

    In this regard, we would like to seek assistance from you.

    We would like to know whether the bills they filed from previous 13th Congress were re-filed in the 14th Congress. We hope that you could help us in knowing what Excel command or function could make our study easier and faster.

    Most of the bill titles are very long such as AN ACT STRENGTHENING THE FAMILY AND PRESERVING THE SOLEMNITY OF MARRIAGE CEREMONY REPEALING AND AMENDING CERTAIN PROVISIONS OF EXECUTIVE ORDER NO. 209 OTHERWISE KNOWN AS THE FAMILY CODE OF THE PHILIPPINES AS AMENDED.

    That’s why it is hard for us to really do simple Excel IF function or manually look it up from one Excel sheet to another Excel sheet. In the past three decades, we have already 8 Congresses.

    And since most of their bills are mere rehashed, sometimes lawmakers do some tweaking by changing the title to make it current. For instance, a lawmaker puts a title “Reproductive Health of 2005.” The bill was archived from the previous Congress. Because he’s reelected in the present Congress, he again refiled the bill and renamed the title “Reproductive Health of 2008.”

    We hope that the command is not too strict to account for such variance.

    Please do help us as we would like to shed light on this issue. Here is my email: alliage.morales[at]gmail.com

    Yours respectfully,

    John Alliage Morales
    [IMG]file:///C:%5CUsers%5CSPECIA%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif[/IMG]Greetings from the Philippines!

    I am working for a news broadcast network in the Philippines. Currently, we are working on a story on the performance of our lawmakers by looking at the quality of bills they file in Congress.

    Our political system allows lawmakers to be reelected on three terms. According to experts, this political set up has allowed this phenomenon: the bills they filed from previous Congress are rehashed in the present Congress. However, empirical data are scarce, or worst non-existent. Experts base their opinion on anecdotal evidence.

    It is in this light that we would like to study this phenomenon. We would like to shed light on the magnitude of this issue by being the first network or agency that would embark on an empirical study.

    We have retrieved from our Congress’ database the list of bills our lawmakers filed in the 13th and 14th Congress. The bills are formatted in Excel format.

    Historically, more than half (of the 200 plus) lawmakers are reelected every Congress, and roughly, they principally author more than 30 bills every Congress. This presents a major obstacle in our study.

    In this regard, we would like to seek assistance from you.

    We would like to know whether the bills they filed from previous 13th Congress were re-filed in the 14th Congress. We hope that you could help us in knowing what Excel command or function could make our study easier and faster.

    Most of the bill titles are very long such as AN ACT STRENGTHENING THE FAMILY AND PRESERVING THE SOLEMNITY OF MARRIAGE CEREMONY REPEALING AND AMENDING CERTAIN PROVISIONS OF EXECUTIVE ORDER NO. 209 OTHERWISE KNOWN AS THE FAMILY CODE OF THE PHILIPPINES AS AMENDED.

    That’s why it is hard for us to really do simple Excel IF function or manually look it up from one Excel sheet to another Excel sheet. In the past three decades, we have already 8 Congresses.

    And since most of their bills are mere rehashed, sometimes lawmakers do some tweaking by changing the title to make it current. For instance, a lawmaker puts a title “Reproductive Health of 2005.” The bill was archived from the previous Congress. Because he’s reelected in the present Congress, he again refiled the bill and renamed the title “Reproductive Health of 2008.”

    We hope that the command is not too strict to account for such variance.

    Please do help us as we would like to shed light on this issue. Here is my email: alliage.morales[at]gmail.com

    Yours respectfully,

    John Alliage Morales
    [IMG]file:///C:%5CUsers%5CSPECIA%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif[/IMG]Greetings from the Philippines!

    I am working for a news broadcast network in the Philippines. Currently, we are working on a story on the performance of our lawmakers by looking at the quality of bills they file in Congress.

    Our political system allows lawmakers to be reelected on three terms. According to experts, this political set up has allowed this phenomenon: the bills they filed from previous Congress are rehashed in the present Congress. However, empirical data are scarce, or worst non-existent. Experts base their opinion on anecdotal evidence.

    It is in this light that we would like to study this phenomenon. We would like to shed light on the magnitude of this issue by being the first network or agency that would embark on an empirical study.

    We have retrieved from our Congress’ database the list of bills our lawmakers filed in the 13th and 14th Congress. The bills are formatted in Excel format.

    Historically, more than half (of the 200 plus) lawmakers are reelected every Congress, and roughly, they principally author more than 30 bills every Congress. This presents a major obstacle in our study.

    In this regard, we would like to seek assistance from you.

    We would like to know whether the bills they filed from previous 13th Congress were re-filed in the 14th Congress. We hope that you could help us in knowing what Excel command or function could make our study easier and faster.

    Most of the bill titles are very long such as AN ACT STRENGTHENING THE FAMILY AND PRESERVING THE SOLEMNITY OF MARRIAGE CEREMONY REPEALING AND AMENDING CERTAIN PROVISIONS OF EXECUTIVE ORDER NO. 209 OTHERWISE KNOWN AS THE FAMILY CODE OF THE PHILIPPINES AS AMENDED.

    That’s why it is hard for us to really do simple Excel IF function or manually look it up from one Excel sheet to another Excel sheet. In the past three decades, we have already 8 Congresses.

    And since most of their bills are mere rehashed, sometimes lawmakers do some tweaking by changing the title to make it current. For instance, a lawmaker puts a title “Reproductive Health of 2005.” The bill was archived from the previous Congress. Because he’s reelected in the present Congress, he again refiled the bill and renamed the title “Reproductive Health of 2008.”

    We hope that the command is not too strict to account for such variance.

    Please do help us as we would like to shed light on this issue. Here is my email: alliage.morales[at]gmail.com

    Yours respectfully,

    John Alliage Morales
    [IMG]file:///C:%5CUsers%5CSPECIA%7E1%5CAppData%5CLocal%5CTemp%5Cmsohtml1%5C01%5Cclip_image001.gif[/IMG]

  10. #10
    I may not understand for sure what you are requiring, but you might be able to use the Autofilter feature. i have attached a file with some dummy data to show you how it works if you have never used it. If fior example all you can do is say search for all records that contain the word Health or some combination thereof in the Title of the bills than the autofilter might work for you.
    Attached Files Attached Files

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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