Question

An auditor found an expense receipt for $200.22 without a name. Write a left table lookup function that would identify the sales rep in the Travel Expenses data Click the icon to view the Travel Expenses data Write a left table lookup function that would identify the sales rep in the Travel Expenses data. Fill in the missing expense receipt value and find the expression the expense receipt (Type an integer or a decimal. Do not round.) А B с D E F 1 Day (March 2014) Sales Rep Expenses 2 28 Jones 168.67 Expense 3 18 Crawford 206.43 Name 4 22 Jones 155 58 5 29 Crawford 138.43 6 16 Jones 260 28 7 2 Crawford 221.36 B 6 Smith 174.00 9 9 Smith 129.96 10 25 Crawford 107 99 -INDEX(B2_C21 MATCH(F2,C2,C210),1) 11

Answer 1

There are certain limitations with using LOOKUP.

LOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value. Now if your spreadsheet isn't built this way, then arrange the columns in your spreadsheet in the order such that the column in which you lookup the values should always be to the left of the column conataining return values. i.e, in the given question the Expenses column should be to the left of the sale rep column in the spreadsheet.

since you have to lookup the expenses value keep the expenses column as first column in the spreadsheet.Since it is a vertical table use VLOOKUP Function

=VLOOKUP(Your lookup value,Your table array,the return value column number in table array,0)

After arranging the array, here I have created a cell with expense and other cell with name. The expenses value i have to lookup to be mentioned in the cell and the return value will i.e Name od sale rep will be displayed since i used vlookup function. Here the formula given is

=VLOOKUP(Expense cell reference, table array,the sale rep column is 3rd column in table so 3 is mentioned, since i need eaxct match we have to mention 0)  

And the other way without arranging the columns instead of using LOOKUP function use the combination of INDEX and MATCH functions.

we'll use the MATCH function to find 200.22 in the range C2:C22. It's found in row 20. Then, INDEX uses that value as the lookup argument, and finds the Name of Sale rep for 200.22 in the 2nd column . The picture is attached below.

Ne File Home Insert Page Layout Formulas Data Review View * Cut Calibri 11 A A Copy - Wrap Text Genera Paste B I U. Format Painter Merge & Center G H Clipboard Font Alignment F5 fx =INDEX(A2:C22,MATCH(F4,C2:C22,0),2) А B с D E F 1 2 Day(March 2014) Sales Rep Expenses 3 28 Jones 168.67 18 Crawforward 206.43 Expenses 149.28 5 22 Jones 155.58 Name Jones 6 29 Crawforward 138.43 16 Jones 260.28 8 2 Crawforward 221.36 9 6 Smith 174 10 9 Smith 129.96 25 Crawforward 107.99 14 Rogers 235.94 13 28 Rogers 179.73 14 19 Smith 110.84 15 7 Crawforward 234.69 16 20 Smith 136.79 17 16 Smith 86.81 18 12 Jones 149.28 19 29 Jones 269.36 20 2 Rogers 200.22 21 3 Jones 93.43 22 11 Rogers 236.68 23 24 25 26 27 28 29 30 31 22 Sheet1 Sheet2 Sheet3 POCO Type here to search SHOT ON POCO F1 O 2