# Thread: Excel Table with IF & Vlookup function Formula Config

1. ## Excel Table with IF & Vlookup function Formula Config

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

2. 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. See if this is what you had in mind

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

5. Originally Posted by tommyt61
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. You're welcome.

7. ## Solved-excel table with IF and VlookupFunction formula Config

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

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

Have a good one !

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. 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.

Page 1 of 2 1 2 Last