Rob has attended:
Excel Intermediate course
VLOOK hidden rows
With the help of the forum I've learned VLOOKUP since my course :-). However, little things catch me out.
Can I search for values in a spreadsheet where I've hidden unnecessary rows?
Can I order the values I'm asking Excel to look for in ascending order for part of a spreadsheet (I'm filtering, maybe I shouldn't)?
What happens when the values excel is looking for are multiple, e.g. 3 rows of data for the same student number? I guess Excel just chooses the first one.
Kind regards,
Rob
RE: VLOOK hidden rows
Hi Rob,
Thanks for your post. A Vlookup formula will find data in hidden rows, so you can hide data in the table. When you sort only part of your data ascending, you can only use TRUE in the Vlookup if you only make the sorted part of your data the Table Array in the formula. If any part of your Table Array is not sorted ascending, you will need to use FALSE in the Vlookup, otherwise, the Vlookup won't always find the correct record. When two or more records have the same ID, the Vlookup will stop at the first one it finds. If you want to find the correct row in records with the same ID, you have two options. 1. You could add a column at the beginning of your data with unique numbers to uniquely identify each record. 2. You could search using criteria from two columns if there are unique items in different records with the same ID. Please have a look at the link below, as it explains the process better than I can in this forum box.
https://support.microsoft.com/en-us/kb/214142
This article demonstrates using Index and Match to find a record using two criteria.
I hope this helps!
Kind regards
Marius Barnard
Excel Trainer
