Spreadsheets
mira2602
Registered Posts: 29 Regular contributor ⭐
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
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=KXhM-wBM7U4
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
- 12.5K For AAT students
- 318 NEW! Qualifications 2022
- 161 General Qualifications 2022 discussion
- 11 AAT Level 2 Certificate in Accounting
- 56 AAT Level 3 Diploma in Accounting
- 87 AAT Level 4 Diploma in Professional Accounting
- 8.8K For accounting professionals
- 23 coronavirus (Covid-19)
- 272 VAT
- 92 Software
- 273 Tax
- 135 Bookkeeping
- 7.2K General accounting discussion
- 200 AAT member discussion
- 3.8K For everyone
- 38 AAT news and announcements
- 345 Feedback for AAT
- 2.8K Chat and off-topic discussion
- 582 Job postings
- 16 Who can benefit from AAT?
- 36 Where can AAT take me?
- 42 Getting started with AAT
- 26 Finding an AAT training provider
- 48 Distance learning and other ways to study AAT
- 25 Apprenticeships
- 66 AAT membership