Spreadsheets

mira2602

Hello there, just wanted to ask for help with formulas-vlookup, if...kind of doesn't seems to sink in at all. Any advice? Maybe I am looking at them from wrong angle.

Many thanks in advance

janwal

Don't know if this will help, but I find U tube very handy

http://www.youtube.com/watch?v=KXhM-wBM7U4

Jan

Louise89

When you type in a lookup if you look below the cell that you are typing into it tells you what you need to put in.

For example;

=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Lookup_value = the cell you are referencing

Table_array = the table in which you are trying to find the referenced value

Col_index_num = the column you are trying to get values from

Range_lookup = True - if you want an approx match (be careful with this one, you probably wont even use it)
= False - an exact match


And remember when pulling down the formula to F4 the table array otherwise it probably wont work.

I've got my SPSW exam tomorrow, so I know how you're feeling... GOOD LUCK!

mrb82

I talk myself through the vlookup like this (this is all inside the brackets, btw)

Look for this cell, against this range of cells, bring back the result from column 10 (so for J you put 10), bring back false if it doesn't match

so...

=vlookup(A1,[workbook]sheet1!A:J,10,false)

where I've put [workbook]sheet1! it just means i've clicked the range of cells I want to search against in another workbook. It should automatically do this for you, you just click what you want.

The value you are searching for (A1) will need to also be the start of the cells you're searching against (so A to J, A1 will need to match the data in A), you're then specifying which column you want it to bring back the information for (So for A:J, you're saying if A matches A1, then bring back the information from cell J).

Oh, and if you're not selecting whole columns, just a range of cells (say A1 to C10), then press F4 to "lock" the data selection, else when you copy and paste the formula, it will shift the selection along (A1:C10 then becomes A2:C11 then A3:C12 and so on).

Does this make sense?

anisurrahman

Hi Mira

If you need any help with Excel formula.. just give me a shout..

Ronnie

mira2602

Guys, thank you very much for yours replies. Can I ask what happend when you combined IF FORMULA with VLOOKUP, for example?

I might seem stupd, but what does '''','''', in formula means?
When doing revision, I came across the '''' sign within the answer.
Many thanks in advance. Well appreciated.

anisurrahman

Hi Mira

IF formula with VLookup function can be used to return exact values if found, and an empty string if not found.


Example: Start the VLookup formula with an IF formula using the ISNA function to check for an #N/A error:

=IF(ISNA(VLOOKUP(G12,D12:E18,2,FALSE)),"",VLOOKUP(G12,D12:E18,2,FALSE))

Double quotes " " use in IF statement formula for text value

For example:

=IF(A1=20,"This is Text 1","This is Text 2")

If yo use number or numerical value as your IF statement True or False value then you don't have to use " "
For example:

=IF(A8="Apple",20,0)

For more IF functions please click on this link: http://www.cpearson.com/excel/nested.htm

Cheers

Ronnie

mira2602

Hi Ronnie, thank you for your reply, sorry did not have time as had some family emergency and even had to postpone my simulation. But here I am, back to studies. Thanks again.