Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vlookup function

0

Hello there, I wanna know how to pull out data from sheet of Data in the right table that consists of only 2 columns to sheet of Test 1 in cell E13 & E27. 

Sorry if there are some words you dont know the meaning in the tables because it's Indonesian. Anyway it seems like i have to combine the Vlookup function with IF or IFS but i really dont know how to compose the formula if i also want the result to be in format of percentage. I have to use Vlookup because the cell in the green colour means only Vlookup function use is allowed. This test was given by the recruiters in the company I had applied.

Also, as seen in the sheet of Data, the Diskon column there has data that is in general number format, thats why i want to know how to use the combined Vlookup function to reach the wanted result in which the cell E13 & E27 will have the number of discount in percentage number format.

I hope you understand my explanation and what I'm looking for. I am waiting for your reply soon. Thanks.

excel
MS Office Version: Fida_mutia
posted by: Fida_mutia (rep: 28) on Tue May 19, 2020 at 2:56 am
Answer
Discuss

Answers

1
Selected Answer

Hello, Fida_mutia

I guess you missunderstood the values in column Diskon. It is 5% if the value is equal or grater than 100,00 and less than 250,000. 10% if the value is equal or grater than 250,000 and less than 500,000 and go on.

I this case, you dont have to combine functions, just change the las parameter in vlookup function from 0 to 1.

On the other hand, to format the vlookup result you can use the TEXT function and use the format "0 %". So, the formula would be the following:

     =TEXT(VLOOKUP(E26,Data!F4:G7,2,1)/100,"0 %")

Note you have to divide by 100 if you want percentage. I attached your file back with the sugessted formula. I hope this help you.

Regards
Basilio

posted by: Basilio (rep: 105) on Tue May 19, 2020 at 11:01 am
Discuss

Discussion

Thank you so much for the answer, it works but I have to add the Value function before Text so that I can calculate it with the total sales in the cell above discount percentage. Btw is there a possible way that the formula starts with Vlookup and then the other functions? If so, how is it? 
Fida_mutia (rep: 28) May 20, '20 at 2:47 pm
Hi, Fida_mutia

You can take TEXT function off. This function is juts to format the number like a percentage. So, you have different options:

1. =VLOOKUP(E26,Data!F4:G7,2,1)   then your result wil be 15, and you can make calculations with it.

2. =VLOOKUP(E26,Data!F4:G7,2,1)/100 then the result will be 0.15, if you change the format of the cell to percentage you get 15%, and you can make calculations as well.

First option, be carefull, if you format the cell as percentage you will get 1500%

I hope to be clear and usefull.

Regards Basilio
Basilio (rep: 105) May 20, '20 at 3:27 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login