No problems. I sent that first post from work, so I can actually take a bit of time on it now. (plus I'm on my second alcoholic beverage for the evening, long day)
OK, I'll explain VLookup first, because I've used that one. HLookup is very similar, but we'll ignore it for now. (Note, I usually only do VBA so if I'm slightly wrong, someone will correct me. Trust me, I
will be corrected, I'm not naming any names though
)
OK say you create a table in Excel that looks like this:<pre><U> A B C D E F</U>
1 | A 1 a
2 | B 2 b
3 | C 3 c
4 | D 4 d
5 | E 5 e</pre>
(This is a pathetic representation of an Excel spreadsheet with data in A1:C5)
Now, in cell F1 we're going to enter this formula:
=VLOOKUP(E1,A1:C5, 2)
In cell E1 type in A, B, C, D or E. The value in F1 will now be 1,2,3,4 or 5 depending what value is in E1.
How it works:
In this VLOOKUP formula there are three things
1. E1 - This is the cell that we are using to hold our text that we are referencing. You could just as easily change E1 for "A" (including quotes) to return the value of 1.
2. A1:C5 - we are telling VLOOKUP that this is the range that contains the data table.
3. 2 - this is telling VLOOKUP to get the value from column 2 that is in the same row as the letter we are telling it to look for. e.g. "A".
So basically VLOOKUP will look in the first column of a table (e.g. column A in range A1:C5) and try to find the text that we specify (e.g. "A"). We tell VLOOKUP which column we'd like the data from if it finds a match (in this case column 2 or column B).
To further illustrate, change the "2" in F1's formula to be a 3. The values that will be returned will be a,b,c,d or e, dependent upon the value in cell E1.
I hope this makes sense.
Now, for HLOOKUP. HLOOKUP does exactly the same thing as VLOOKUP except that HLOOKUP looks in the first row of the table and NOT the first column. You tell HLOOKUP what row you would like data from as opposed to which column.
If you need any further explanation, just repost.
This message was edited by Mark O'Brien on 2002-04-08 18:35
This message was edited by Mark O'Brien on 2002-04-08 18:36
This message was edited by Mark O'Brien on 2002-04-08 21:54