Excel lookup functions ppt Memphis

Excel Lookup Functions Ppt Memphis-PDF Download

  • Date:23 May 2020
  • Views:12
  • Downloads:0
  • Pages:5
  • Size:773.46 KB

Share Pdf : Excel Lookup Functions Ppt Memphis

Download and Preview : Excel Lookup Functions Ppt Memphis


Report CopyRight/DMCA Form For : Excel Lookup Functions Ppt Memphis


Description:

Excel Lookup Functions The format of the VLOOKUP function is VLOOKUP lookup value table array col index num range lookup lookup value the value to be serachfor in the first column of the array table array is the table of two or more columns of data col index num is the column number in table array from which the matching value must be returned range lookup is a logical value that specifies

Transcription:

CIVL 1112 Excel Lookup Functions 2 5,Excel Lookup Functions Excel Lookup Functions. VLOOKUP lookup value table array VLOOKUP lookup value table array. col index num range lookup col index num range lookup. The lookup value can be a value a reference or a text If col index num is less than 1 VLOOKUP returns the. string VALUE error value, The table array is a reference to a range If col index num is greater than the number of columns. in table array VLOOKUP returns the REF error value. A col index num of 1 returns the value in the first. column in table array a col index num of 2 returns. the value in the second column in table array and so on. Excel Lookup Functions Excel Lookup Functions, VLOOKUP lookup value table array VLOOKUP lookup value table array. col index num range lookup col index num range lookup. If range lookup is TRUE or omitted an approximate If range lookup is TRUE the values in the first column. match is returned of table array must be placed in ascending order 2. 1 0 1 2 A Z FALSE TRUE otherwise VLOOKUP, In other words if an exact match is not found the next may not give the correct value. largest value that is less than lookup value is returned. If range lookup is FALSE table array does not need to. If range lookup is FALSE VLOOKUP will find an exact. If one is not found the error value N A is returned. Excel Lookup Functions VLOOKUP Examples,D E F G H I.
VLOOKUP lookup value table array 17,col index num range lookup 18 x x2 x3 x4. 19 0 9 0 8 0 7 0 7,20 1 5 2 3 3 4 5 1, You can put the values in ascending order by choosing 21 2 2 4 8 10 6 23 4. the Sort command from the Data menu and selecting 22 2 5 6 3 15 6 39 1. Ascending 23 3 1 9 6 29 8 92 4,24 4 8 23 0 110 6 530 8. The values in the first column of table array can be text. numbers or logical values,VLOOKUP 1 E19 H24 1 TRUE returns 0 9. Uppercase and lowercase text are equivalent,CIVL 1112 Excel Lookup Functions 3 5.
VLOOKUP Examples VLOOKUP Examples,D E F G H I D E F G H I. 2 3 4 2 3 4,18 x x x x 18 x x x x,19 0 9 0 8 0 7 0 7 19 0 9 0 8 0 7 0 7. 20 1 5 2 3 3 4 5 1 20 1 5 2 3 3 4 5 1,21 2 2 4 8 10 6 23 4 21 2 2 4 8 10 6 23 4. 22 2 5 6 3 15 6 39 1 22 2 5 6 3 15 6 39 1,23 3 1 9 6 29 8 92 4 23 3 1 9 6 29 8 92 4. 24 4 8 23 0 110 6 530 8 24 4 8 23 0 110 6 530 8, VLOOKUP 1 E19 H24 1 FALSE returns N A VLOOKUP 0 8 E19 H24 1 returns N A.
VLOOKUP Examples VLOOKUP Examples,D E F G H I D E F G H I. 2 3 4 2 3 4,18 x x x x 18 x x x x,19 0 9 0 8 0 7 0 7 19 0 9 0 8 0 7 0 7. 20 1 5 2 3 3 4 5 1 20 1 5 2 3 3 4 5 1,21 2 2 4 8 10 6 23 4 21 2 2 4 8 10 6 23 4. 22 2 5 6 3 15 6 39 1 22 2 5 6 3 15 6 39 1,23 3 1 9 6 29 8 92 4 23 3 1 9 6 29 8 92 4. 24 4 8 23 0 110 6 530 8 24 4 8 23 0 110 6 530 8, VLOOKUP 3 E19 H24 3 returns 15 6 VLOOKUP 3 E19 H24 3 FALSE returns N A.
VLOOKUP Examples VLOOKUP Examples,D E F G H I A B C D E F G H I J K. 18 x x2 x3 x4 3 Maximum aggregate size in, 4 Slump in 0 375 0 500 0 750 1 000 1 500 2 000 3 000 6 000. 19 0 9 0 8 0 7 0 7,5 1 to 2 350 335 315 300 275 260 220 190. 20 1 5 2 3 3 4 5 1 6 3 to 4 385 365 340 325 300 285 245 210. 21 2 2 4 8 10 6 23 4 7 6 to 7 410 385 360 340 315 300 270. 22 2 5 6 3 15 6 39 1,9 Air Content 3 0 2 5 2 0 1 5 1 0 0 5 0 3 0 2. 23 3 1 9 6 29 8 92 4 10,24 4 8 23 0 110 6 530 8,VLOOKUP 3 to 4 B4 J7 4 returns 340.
VLOOKUP 2 2 E19 H24 4 returns 23 4,CIVL 1112 Excel Lookup Functions 4 5. VLOOKUP Examples VLOOKUP Examples,A B C D E F G H I J K A B C D E F G H I J K. 3 Maximum aggregate size in 3 Maximum aggregate size in. 4 Slump in 0 375 0 500 0 750 1 000 1 500 2 000 3 000 6 000 4 Slump in 0 375 0 500 0 750 1 000 1 500 2 000 3 000 6 000. 5 1 to 2 350 335 315 300 275 260 220 190 5 1 to 2 350 335 315 300 275 260 220 190. 6 3 to 4 385 365 340 325 300 285 245 210 6 3 to 4 385 365 340 325 300 285 245 210. 7 6 to 7 410 385 360 340 315 300 270 7 6 to 7 410 385 360 340 315 300 270. 9 Air Content 3 0 2 5 2 0 1 5 1 0 0 5 0 3 0 2 9 Air Content 3 0 2 5 2 0 1 5 1 0 0 5 0 3 0 2. VLOOKUP 1 to 2 B4 J7 9 returns 190 VLOOKUP 1 to 7 B4 J7 1 returns 1 to 2. Excel MATCH Function Excel MATCH Function, The MATCH function returns the relative position MATCH lookup value lookup array match type. of an item in an array that matches a specified,value in a specified order. The lookup value is the value you want to match in. lookup array,MATCH lookup value lookup array match type.
For example when you look up someone s number in a. lookup value is the value you want to match in the lookup array. telephone book or online you are using the person s. name as the lookup value but the telephone number is. lookup array is a contiguous range of cells containing possible the value you want. lookup values, match type is the number 1 0 or 1 Lookup value can be a value number text or logical. value or a cell reference to a number text or logical. Excel MATCH Function Excel MATCH Function, MATCH lookup value lookup array match type MATCH lookup value lookup array match type. If match type is 1 MATCH finds the largest value that is less than MATCH does not distinguish between uppercase and. or equal to lookup value Lookup array must be placed in lowercase letters when matching text values. ascending order 2 1 0 1 2 A Z FALSE TRUE, If MATCH is unsuccessful in finding a match it returns. If match type is 0 MATCH finds the first value that is exactly equal the N A error value. to lookup value Lookup array can be in any order,If match type is 0 and lookup value is text. If match type is 1 MATCH finds the smallest value that is greater lookup value can contain the wildcard characters. than or equal to lookup value Lookup array must be placed in asterisk and question mark. descending order TRUE FALSE Z A 2 1 0 1 2 and so on. An asterisk matches any sequence of characters a question mark. If match type is omitted it is assumed to be 1 matches any single character. CIVL 1112 Excel Lookup Functions 5 5,VLOOKUP Examples VLOOKUP Examples.
D E F G H I D E F G H I,2 3 4 2 3 4,18 x x x x 18 x x x x. 19 0 9 0 8 0 7 0 7 19 0 9 0 8 0 7 0 7,20 1 5 2 3 3 4 5 1 20 1 5 2 3 3 4 5 1. 21 2 2 4 8 10 6 23 4 21 2 2 4 8 10 6 23 4,22 2 5 6 3 15 6 39 1 22 2 5 6 3 15 6 39 1. 23 3 1 9 6 29 8 92 4 23 3 1 9 6 29 8 92 4,24 4 8 23 0 110 6 530 8 24 4 8 23 0 110 6 530 8. MATCH 4 0 F19 F24 1 returns 2 MATCH 4 0 F19 F24 0 returns N A. VLOOKUP Examples Lookup Function Example,D E F G H I A B C D E F G H I J K.
18 x x x x 3 Maximum aggregate size in, 4 Slump in 0 375 0 500 0 750 1 000 1 500 2 000 3 000 6 000. 19 0 9 0 8 0 7 0 7,5 1 to 2 350 335 315 300 275 260 220 190. 20 1 5 2 3 3 4 5 1 6 3 to 4 385 365 340 325 300 285 245 210. 21 2 2 4 8 10 6 23 4 7 6 to 7 410 385 360 340 315 300 270. 22 2 5 6 3 15 6 39 1,9 Air Content 3 0 2 5 2 0 1 5 1 0 0 5 0 3 0 2. 23 3 1 9 6 29 8 92 4 10,24 4 8 23 0 110 6 530 8, Write one function or a series of nested functions that. return the amount water required for a concrete mix. MATCH 4 0 F19 F24 1 returns N A based on the slump and the maximum aggregate size. Hint Consider VLOOKUP and MATCH,Lookup Function Example Excel Lookup Functions.
A B C D E F G H I J K,3 Maximum aggregate size in, 4 Slump in 0 375 0 500 0 750 1 000 1 500 2 000 3 000 6 000. 5 1 to 2 350 335 315 300 275 260 220 190,6 3 to 4 385 365 340 325 300 285 245 210. 7 6 to 7 410 385 360 340 315 300 270,9 Air Content 3 0 2 5 2 0 1 5 1 0 0 5 0 3 0 2. VLOOKUP 3 to 4 B4 J7 MATCH 0 75 B4 J4,returns 340,.

Related Books