• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Select a formula based on a criteria using Vlookup

V

Vijayonline2008

New Member
Name Region Value Result
Vijay East 50 25.00
Sam West 60 20.00
Raj North 45 11.25
Michael East 33 16.50
George North 89 22.25
Melvin West 95 31.67
Helen North 10 2.50
Rajesh East 25 12.50
Arnold West 75 25.00
Trump East 64 32.00

In the excel file attached I have used IF formula to get the Result in D column,if the region is East value will be divided by 2 for West value will be divided by 3 and if if it is North value will be divided by 4.

So instead of using IF can we use VLOOKUP or any other function to paste formulas directly by comparing the regions. As Nested IFs are very difficult to maintain.
 

Attachments

  • Sales.xlsx
    10.4 KB · Views: 5
Try........

One of the fomula, copy down :

=C2/MATCH(B2,{"","East","West","North"},0)

or,

=C2/MATCH(LEFT(B2),{"","E","W","N"},0)

or,

=C2/TEXT(CODE(B2)-78,"3;2;4")

or,

=C2/FIND(LEFT(B2),{" EWN"})

Regards
 

Attachments

  • Sales.xlsx
    11.2 KB · Views: 3
Last edited:
That was perfect but I'am reframing the question.
Now for East I Want to use the formula C2*9/75 instead of divided by 2 for West C3+25/3*75 instead of divided by 3 and for North C4*30% instead of divided by 4.
 
Ok instead of using IF can we use VLOOKUP. Say in the same sheet I will put the three regions in G coloumn and against that the corresponding formula in H column as given below.

East C2*9/75
West C3+25/3*75
North C4*30%

Now from D column using Vlookup formula can we compare the region in the B column and extract the formula that matches the region from H column. This is what I want as I don't want to use the NestedIFs ,I have 10 different formulas for seperate regions and the example sheet is just a short portion of it.
 
Vijayonline2008 said:
Ok instead of using IF can we use VLOOKUP. Say in the same sheet I will put the three regions in G coloumn and against that the corresponding formula in H column as given below.

East C2*9/75
West C3+25/3*75
North C4*30%

Now from D column using Vlookup formula can we compare the region in the B column and extract the formula that matches the region from H column. This is what I want as I don't want to use the NestedIFs ,I have 10 different formulas for seperate regions and the example sheet is just a short portion of it.
Click to expand...

1] Not clear in your description, upload a revised file and state the expected result as per post #.1.

2] Here's the formula in using VLOOKUP instead of IF function :

=C2*VLOOKUP(B2,{"East",0.12;"North",0.3;"West",1},2,0)+IF(B2="West",25/3*75,0)

Regards
 
Fresh Excel file uploaded.Have done the Vlookup from D column. Now the value which we have got in D column should be changed to formula by adding an equal sign. I tried this one but it is not working ="="&VLOOKUP(B2,$G$2:$H$4,2,0).
 

Attachments

  • Sales.xlsx
    10.4 KB · Views: 8
I opened the file but all the answers of the region east is same (6) and all the answers of the west is same (685). but it should be different right.
 
I'll try to explain (my english is not my native language, sorry)

1. Convert data range to table: Select any cell of the range, and press Ctrl + T, and then click on Accept (or OK).
2. Change table name to Tbl: Select any cell of the table, go to Table Tools and change the name (left of the ribbon).

The formulas uses a structural refference, proper to tables. Instead of C2, you must use Tbl[@Value] (Each Cel from Column Value in the Table Tbl)

Next, you must create a name with formulas. Press Ctrl + F3 and press new. There, in the textbox appear, you must put the formula:

=EVALUATE(VLOOKUP(Sheet1!$B2,Sheet1!$G$2:$H$4,2,))+0*TODAY()

The VLOOKUP part get the formula to use, depends on Each Region. The EVALUATE part converts the text obtain into a formula. Finally, the 0*TODAY() part make the formula volatile, this help to calculate the formula when the data changes.

I hope you understand. Blessings!
 
I worked out as per your instruction and it is working fine. Please clarify these doubts.
1. =EVALUATE(VLOOKUP(Sheet1!C2,Sheet1!$E$2:$F$4,2,0)) , I used this formula without the 0*TODAY() and it is working. Also i tried changing the value then also this is working.
2. By entering Tbl[@Value] how the excel knows it should pick the value from C2 column.
 
Hi again @Vijayonline2008

Answering to your questions:

1. The formula worked if you quit the 0*TODAY() part. If you change the formula es in column H, worked dinamically too, but if you change the values on column C the values not update (try yourself). This is the reason for use 0*TODAY()

2. Check this link, learn about structural references:
http://chandoo.org/wp/2013/06/26/introduction-to-structural-references/

Blessings!
 
You must log in or register to reply here.
Back
Top