how to look up what curency is worth

MS Excel: How to utilize the LOOKUP Function (WS)

This Excel tutorial explains how to use the Excel LOOKUP office with syntax and examples.

Description

The Microsoft Excel LOOKUP part returns a value from a range (one row or i column) or from an array.

The LOOKUP function is a congenital-in function in Excel that is categorized every bit a Lookup/Reference Function. It can exist used as a worksheet role (WS) in Excel. As a worksheet function, the LOOKUP function can be entered as function of a formula in a cell of a worksheet.

There are 2 different syntaxes for the LOOKUP function:

LOOKUP Function (Syntax #1)

In Syntax #i, the LOOKUP role searches for value in the lookup_range and returns the value in the result_range that is in the same position.

The syntax for the LOOKUP function in Microsoft Excel is:

LOOKUP( value, lookup_range, [result_range] )

Parameters or Arguments

value

The value to search for in the lookup_range.

lookup_range

A single row or single cavalcade of data that is sorted in ascending order. The LOOKUP function searches for value in this range.

result_range

Optional. It is a single row or unmarried column of data that is the aforementioned size equally the lookup_range. The LOOKUP function searches for the value in the lookup_range and returns the value from the same position in the result_range. If this parameter is omitted, information technology will return the first column of data.

Returns

The LOOKUP function returns any datatype such as a cord, numeric, engagement, etc.
If the LOOKUP function can not find an exact match, information technology chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP part will render #N/A.
If the values in the LOOKUP_range are not sorted in ascending society, the LOOKUP office will render the wrong value.

Example (as Worksheet Function)

Allow's look at some Excel LOOKUP function examples and explore how to use the LOOKUP office as a worksheet function in Microsoft Excel:

Based on the Excel spreadsheet above, the following LOOKUP examples would return:

=LOOKUP(10251, A1:A6, B1:B6)          Result:          "Pears"  =LOOKUP(10251, A1:A6)          Upshot:          10251  =LOOKUP(10246, A1:A6, B1:B6)          Issue:          #N/A  =LOOKUP(10248, A1:A6, B1:B6)          Result:          "Apples"

LOOKUP Function (Syntax #2)

In Syntax #2, the LOOKUP office searches for the value in the first row or cavalcade of the array and returns the corresponding value in the concluding row or column of the array.

The syntax for the LOOKUP function in Microsoft Excel is:

LOOKUP( value, array )

Parameters or Arguments

value

The value to search for in the array. The values must be in ascending order.

array

An array of values that contains both the values to search for and return.

Returns

The LOOKUP function returns whatever datatype such as a string, numeric, date, etc.
If the LOOKUP function can not observe an verbal match, it chooses the largest value in the lookup_range that is less than or equal to the value.
If the value is smaller than all of the values in the lookup_range, then the LOOKUP role will return #N/A.
If the values in the array are not sorted in ascending guild, the LOOKUP role volition return the incorrect value.

Example (as Worksheet Office)

Let'southward look at some Excel LOOKUP function examples and explore how to use the LOOKUP part every bit a worksheet function in Microsoft Excel:

=LOOKUP("T", {"due south","t","u","v";10,11,12,thirteen})          Result:          eleven  =LOOKUP("Tech on the Internet", {"s","t","u","v";x,xi,12,13})          Result:          xi  =LOOKUP("t", {"s","t","u","5";"a","b","c","d"})          Outcome:          "b"  =LOOKUP("r", {"s","t","u","v";"a","b","c","d"})          Upshot:          #Northward/A  =LOOKUP(2, {1,2,3,four;511,512,513,514})          Consequence:          512

Frequently Asked Questions

Question: In Microsoft Excel, I take a table of information in cells A2:D5. I've tried to create a simple LOOKUP to find CB2 in the data, but information technology always returns 0. What am I doing wrong?

Answer: Using the LOOKUP function tin can sometimes be a bit tricky then allow's look at an example. Below nosotros have a spreadsheet with the data that you described.

In cell F1, nosotros've placed the following formula:

=LOOKUP("CB2",A2:A5,D2:D5)

And yes, fifty-fifty though CB2 exists in the data, the LOOKUP function returns 0.

At present, allow'southward explain what is happening. At offset, information technology looks like the function isn't finding CB2 in the list, merely in fact, information technology is finding something else. Permit'southward fill up in the empty cells in D3:D5 to explicate better.

If nosotros place the values TEST1, TEST2, TEST3 in cells D3, D4, 5, respectively, we can run into that the LOOKUP function is in fact returning the value TEST2. So we ask ourselves, when we are looking up CB2 in the data and CB2 exists in the data, why is information technology returning the value for CB19? Good question. The LOOKUP function assumes that the data in column A is sorted in ascending gild.

If you look closer at cavalcade A, it is non in fact sorted in ascending club. If we quickly sorted column A, it would look like this:

Now the LOOKUP function correctly returns 3A when it is looking upwards CB2 in the data.

To avoid these sorting issues with your data, we recommend using VLOOKUP function in this instance. Let's show you how we would do this. If we changed our formula below (only left our data in column A in the original sort order):

The following VLOOKUP formula would return the correct value of 3A.

=VLOOKUP("CB2",$A$2:$D$v,4,Faux)

The VLOOKUP role does not require us to take the information sorted in ascending order since we used FALSE as the last parameter - which means that information technology is looking for an exact match.

---

Question: I have the post-obit LOOKUP formula:

=LOOKUP(C2,{"A","B","C","D","Eastward","F","G","H","I","K","10","Z"}, {"one","2","3","4","v","half-dozen","vii","8","9","10","12","1"})

I also need to add zero to the lookup vector and result vector. How do I exercise this?

Answer: Using numbers in Excel can exist catchy, equally you tin enter them either as numeric or text values. Because of this, there are 2 possible solutions.

Numeric Solution

If you accept entered your nil as a numeric value, then the following formula will work:

=LOOKUP(C2,{0,"A","B","C","D","E","F","G","H","I","G","X","Z"}, {0,"1","two","iii","iv","5","6","seven","8","9","10","12","one"})

Text Solution

If yous have entered your zero as a text value, and so the post-obit formula will work:

=LOOKUP(C2,{"0","A","B","C","D","E","F","G","H","I","One thousand","X","Z"}, {"0","1","2","three","4","v","6","7","8","9","ten","12","1"})

---

Question: For the post-obit function in Microsoft Excel:

=LOOKUP(M14,Sheet2!A2:A2240,Sheet2!B2:B2240)

How practise I get information technology to return a blank cell if the LOOKUP value (M14) is blank?

Answer: To cheque for a blank value in cell M14, you can use the IF part and ISBLANK function as follows:

=IF(ISBLANK(M14),"",LOOKUP(M14,Sheet2!A2:A2240,Sheet2!B2:B2240))

Now if the value in cell M14 is bare, the formula will return a blank. Otherwise information technology will perform the LOOKUP part as before.

bainesanisho.blogspot.com

Source: https://www.techonthenet.com/excel/formulas/lookup.php

Share this post