|
||||||||||||||||||||
|
|||||||||||||||||||||
|
#1
08-16-2008, 03:46 PM
|
|||
|
|||
|
Hlookup help
Hi there,
New here, so please excuse me if I shouldnt be posting this query here  Now my query might be quite rudimentary but please bear with me, I'm a new at Excel.. My problem (as the title suggests) is with HLookup: Heres the problem:-  Now im trying to hlookup so that next to Mark it shows A, next to Kim C.. and so on.. Can't seem to get it?? help? Thanks in advance.. cheers roy 
|
|
#2
08-16-2008, 03:57 PM
|
||||
|
||||
|
You won't be able to do that with HLOOKUP. Here's one way.....
In B11 copied down =LOOKUP(9^9,CHOOSE({1,2,3},MATCH(A11,A$4:A$6,0),MATCH(A11,B$4:B$6,0),MATCH(A11,C$4:C$6,0)),A$3:C$3)
|
|
#3
08-17-2008, 01:20 PM
|
|||
|
|||
|
Quote:
Hey, Thanks a lot.. Worked like a charm.. But I had some problems inapplying the same logic in a larger data. Could you please ellaborate on the bold parts:- =LOOKUP(9^9,CHOOSE({1,2,3},MATCH(A11,A$4:A$6,0),MATCH(A11,B$4:B$6,0),MATCH(A11,C$4:C$6,0)),A$3:C$3) and how it would change with a different data? thanks..
|
|
#4
08-17-2008, 01:33 PM
|
||||
|
||||
|
How large is your real data? There may be better ways.
In the formula I suggested the 9^9 is just designed to be a number greater than any number MATCH function might return so 9^9 should work for you however large your dataset. CHOOSE({1,2,3}.... changes depending on how many columns you have, if you have 5 columns then you need CHOOSE({1,2,3,4,5}.....and you need to include 5 MATCH functions, one for each column. CHOOSE only allows up to 29 arguments so you're limited to that many columns. An alternative approach..... =IF(COUNTIF(A$4:C$6,A11),INDEX(A$3:C$3,MIN(IF(A$4:C$6=A11,COLUMN(A$4:C$6)-COLUMN(A$4)+1))),"Name not listed") confirmed with CTRL+SHIFT+ENTER This can be more easily adapted to different amounts of data, even 100+ columns for instance
|
|
#5
08-18-2008, 02:37 PM
|
|||
|
|||
|
Hey,
Thanks a lot. I usually have to deal with about 10-11 cols, and the 1st formulae was perfect!!.. Not to say that 2nd one wouldn't have been.. but it looked a bit complicated for my liking..:D You haveno idea how much time I saved in Office today.. What usually takes me about 45min.. took me 45 secs !!! I think its about time I start analysing parts of my work I can complete smartly.. Thanks a ton!! roy Last edited by roy1987; 08-18-2008 at 02:59 PM.
|
 |
| Bookmarks |
New topics in Excel 2007 Help
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
