Look up sheet in excel

[Guest_imported]

Hi

I have a lookup sheet, say lSheet.
col1 col2
10 .5
20 .6
30 .4

while working on my current sheet say CSheet i need to feed a formula. which will look for value corresponding to say 10 on lsheet that is value .5

Retreive and incorporate that value in a formula on CSheet

Thanks in Advance
Jagdeep Singh

 

[DaleWatson123321]

Hi Jagdeep,

Steps to use...

1) On the sheet containing your lookup table, assign a range name to the table. In my example, I've used the name "table_1", but you can use whatever name you want.

2) On your other sheet where you want the formula, enter the following formula. For this example, I have entered the formula in Cell B1.

=VLOOKUP(A1,table_1,2,FALSE)

3) In cell A1, enter one of the values to be looked up - i.e. 10, 20, or 30.

Using the "FALSE" argument forces Excel to provide a result ONLY if the number being looked up exists in the table. For example if you entered "10" in cell A1, your answer will be ".5". However, if you entered "11", the result will be "#N/A".

If you were to eliminate the "FALSE" argument, entering "11" in cell A1 will provide ".5" as the result. Entering any number from 10 to 19 will also provide ".5" as the result. And of course entering "20" will provide ".6" as the result.

In case you're unaware, the best method of assigning a range name is as follows:
a) Highlight the cell or range-of-cells
b) Hold down the <Conrol> key and hit <F3>
c) Type the name
d) Hit <Enter>

Caution: Do NOT use names that will conflict with cell coordinates or with VBA commands. For example, don't use a name like &quot;C12&quot; - instead use &quot;C_12&quot; or &quot;_C12&quot;. Don't use a name like &quot;Sheet1&quot; - instead use &quot;Sheet_1&quot;. If in doubt, use of the &quot;_&quot; character is a good choice.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca