Spreadsheets
mira2602
Registered Posts: 29 ? ? ?
Hello there, just wanted to ask for help with formulasvlookup, 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
Many thanks in advance
0
Comments

Don't know if this will help, but I find U tube very handy
http://www.youtube.com/watch?v=KXhMwBM7U4
Jan0 
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!0 
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?0 
Hi Mira
If you need any help with Excel formula.. just give me a shout..
Ronnie0 
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.0 
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
Ronnie0 
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.0
Categories
 All Categories
 1.2K Books to buy and sell
 2.3K General discussion
 18.9K For AAT students
 251 NEW! Qualifications 2022
 139 General Qualifications 2022 discussion
 8 AAT Level 2 Certificate in Accounting
 35 AAT Level 3 Diploma in Accounting
 59 AAT Level 4 Diploma in Professional Accounting
 8.9K For accounting professionals
 23 coronavirus (Covid19)
 274 VAT
 92 Software
 274 Tax
 137 Bookkeeping
 7.3K General accounting discussion
 193 AAT member discussion (AATQB, MAAT, FMAAT and AAT Licensed Accountants and Bookkeepers)
 3.8K For everyone
 39 AAT news and announcements
 352 Feedback for AAT
 2.8K Chat and offtopic discussion
 589 Job postings
 17 Who can benefit from AAT?
 36 Where can AAT take me?
 44 Getting started with AAT
 26 Finding an AAT training provider
 48 Distance learning and other ways to study AAT
 25 Apprenticeships
 65 AAT membership