How to combine Vlookup function with If function in Excel
Vlookup function and If function are important and basic functions in Excel. When combining these two functions, users can use in many calculations, statistics of different data such as showing employee information when just pressing the code. In this article we will show you how to set conditions to filter values, by combining the Vlookup function with the If function.
With certain conditional statistics and require users to fill in information based on that condition, you can use the Vlookup function with the If function, as in the rating table. The following example will help you better understand how to use the Vlookup function and If function to filter conditional values.
- How to automatically display names when entering code in Excel
- How to combine Sumif and Vlookup functions in Excel
How to use Vlookup function with If function
This is the student's test results and pass or score table. We have benchmarked each test block to see if students have enough passing grades, or fail. You need to enter test results for each student.
The If function will look for results that are satisfied with the benchmark, thereby giving a conclusion of passing or failing. Then combine with the VLOOKUP function to filter the values that satisfy each student. Finally, the two functions will produce the search results.
Vlookup function will have a formula:
- VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
If function has a formula:
- IF (Logical_test, Value_if_True, Value_if_False)
Formula when nested Vlookup function and If function together
- IF (Logical_test> = Vlookup (lookup_value, table_array, col_index_num, [range_lookup]), Value_if_True, Value_if_False)
Step 1:
In the first result box, enter the formula to nest 2 functions: = IF (C2> = Vlookup (B2, $ B $ 10: $ C $ 13,2,0), "Beans", "Slide" .
In which C2> = Vlookup with C2 is the Score column that the students have achieved. When C2> = Vlookup satisfies the condition, it will produce a Bean result, otherwise it will not be satisfied.
Next to B2, $ B $ 10: $ C $ 13.2,0 is the group column reference located in column B and the second and line order is 10 to the expenditure column in column C with the final value in line 13.
2, 0 with 2 is the value of column 2 from the reference column in the reference table. 0 is if the value of Vlookup function cannot be found, then returns 0.
Note, to display the listed characters $ you need to localize as usual then press F4.
Step 2:
When you press Enter we will get the result for the first student, Bean, which corresponds to the benchmark below. The first student gets 18 points higher with the A-grade benchmark, so the result will be the Beans as shown.
To perform the results for the remaining students, simply drag the results in the first cell down the lines of the Results column. Finally we will be evaluated as shown in the figure.
Combining two Vlookup functions and such If function will save you time in finding conditional values, without having to filter the value in turn according to the VLOOKUP function and then proceed to the If function. The search table must have at least 2 columns, 1 column contains search criteria, 1 column is the value of that condition.
See more:
- How to use the SUMIF function in Excel
- These are the most basic functions in Excel that you need to understand
- How to print Excel on an A4 page
I wish you all success!
You should read it
- Vlookup function in Excel
- Use VLOOKUP to join two Excel tables together
- How to use VLOOKUP Function in Excel
- How to use the XLOOKUP function in Excel?
- How to combine Sumif and Vlookup functions in Excel
- How to use Hlookup function on Excel
- How to use Vlookup function between 2 sheets, 2 different Excel files
- How to combine Vlookup function with Left function
May be interested
- How to use the XLOOKUP function in Excel?excel's new xlookup function replaces vlookup, which provides a powerful replacement for one of excel's most popular functions. this article will guide you how to use this xlookup function.
- How to filter duplicate data on 2 Excel sheetsto filter duplicate data from 2 sheets in excel, you can use the vlookup function.
- How to use Hlookup function on Excelhlookup function basically also has the function syntax and features like vlookup function, which is to help users find data in excel table, with the conditions or given information. here is the guide for using the hlookup function in detail.
- Look up data in Excel tables: Replace VLOOKUP with INDEX and MATCHquick lookup of data in excel tables: replace vlookup with index and match. do you know yet? let's tipsmake.com find out in the article below!
- VLOOKUP function - Usage and detailed examplesvlookup is one of the most used excel functions. the following article details how to use and detailed examples when using the vlookup function.
- How to automatically display names when entering code in Excel - Combine IF function and Vlookup functioninstead of entering the name and code in excel, users can combine the if function and vlookup function to automatically display the name when entering the code.
- How to automatically display names when entering code in Excelinstead of entering names and entering code in excel, users can combine the if and vlookup functions to automatically display the name when entering the code.
- How to use the IF function with VLOOKUP (examples and how to)how to use the if function in combination with vlookup (examples and how to do it). if you are looking to use the if function with vlookup with specific examples, please refer to the following article. the following article will guide you 3 common cases using the if function
- How to combine 2 columns Full name in Excel does not lose contentin excel, to be able to merge content in 2 columns into a single column without losing content, we need to use calculation functions.
- How to use the FIND function in Excel?the find function in excel is a function that finds characters in a text string, having the same purpose as string separators like the mid function in excel or the left function or the right function.