• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup, Hlookup, Match, Index

dmcnab

New Member
Hi all...I am new to this site, but have some Excel experience. I am having a lot of trouble trying to understand VLOOKUP, HLOOKUP, MATCH, INDEX etc etc. The Help files in Excel are not that useful; I read some stuff on this website, but examples that I can deconstruct work best for me. I would like to upload a small file where I have tried to use all of these formulas to extract data...with no success....is it possible to upload a small Excel sample file with an outline of what I am trying to learn? Thanks.
 
Dmcnab


Firstly Welcome to Chandoo.org Forums


About uploading files, Chandoo.org doesn't have that facility but have a read of http://chandoo.org/forums/topic/posting-a-sample-workbook

for some ideas


You may want to also have a read of

http://chandoo.org/wp/2008/11/19/vlookup-match-and-offset-explained-in-plain-english-spreadcheats/

and

http://chandoo.org/wp/tag/vlookup-week/


Which Discuss VLookup, Match and their uses etc
 
http://www.speedyshare.com/files/28201548/hlookup.xls


Hi Hui....I have uploaded a small sample file at the above url;

I have a file where jobs (123, 456, 789) are assigned to people (BOB1, BOB2, BOB3 etc). I want to reverse that data and use formulas that will show the jobs in columns, dates down the left side and then see who is assigned to each job on any given day. I have tried using the LOOKUP formulas, MATCH and INDEX but can't quite seem to get anything working...hopefully, you can set up the formulas that I can study to learn how these formulas work...thank you
 
N6: =IFERROR(INDEX($C$5:$K$5,,MATCH(N$5,$C6:$K6,0)),"-")

Copy down and across


How does it work

ignore the Iferror bit for now

INDEX($C$5:$K$5,,MATCH(N$5,$C6:$K6,0))

says look in Range c5:k5 (The names) and retrieve a value from the same row, thats the blank between the two ,, and from the Column identified by the MATCH(N$5,$C6:$K6,0)


Now the MATCH(N$5,$C6:$K6,0), looks for the value of N5 in the range $C6:$K6 and returns the position of it, ie 1 to 9 which is used in the Index as the Column No (nothing to do with your column No's in Row 4)


Now a lot of the cells will return errors as there is no match, so the whole

INDEX($C$5:$K$5,,MATCH(N$5,$C6:$K6,0))

is wrapped in an Iferror function and will put a nice "-" where evere an error occurs


Hope that helps
 
Thanks Hui...I tried it but it produces a result of #NAME?...I noticed that you have two , before MATCH and so I removed one but it still didn't work....and I even changed it to ISERROR but that didn't work either..
 
Your using a pre-Excel 2007 version which doesn't have Iferror

Try:

=IF(ISERROR(MATCH(N$5,$C6:$K6,0)),"-",INDEX($C$5:$K$5,,MATCH(N$5,$C6:$K6,0)))


Note the iserror only needs to look at the MATCH(N$5,$C6:$K6,0) as that is where the error is generated as it can't find a value, not the whole INDEX($C$5:$K$5,,MATCH(N$5,$C6:$K6,0)) part of the equation
 
OK...that's works just fine...if I have 123*, or, 123**, or, 123*** (or ***123) in cells N2:N4, how can I adjust the formula....I have tried using an OR in there, but it doesn't work....any suggestions?
 
You can use wildcards by changing

N6 to =IF(ISERROR(MATCH("*"&N$5&"*",$C6:$K6,0)),"-",INDEX($C$5:$K$5,,MATCH("*"&N$5&"*",$C6:$K6,0)))


but you also need to change the Title N5:p5 to text

ie: '123 '456 '789


as well as the Data Entry area the entries will have to be '123

Obviously 123John and Fred123 are text already
 
...hhmmm...so is there no way to accomplish this without changing things to text (ie: '123) b/c the data that is being entered comes from fixed dropdown lists and I don't think it can be changed to text...is there a way to conditionally format all of those cells so that everything will be text?
 
Formating only changes the appearance of things not the actual contents
 
Ok

In N6 you will have to Array Enter the following

Copy it into N6 and press Ctrl Shift Enter instead of the usual Enter

=IF(ISERROR(MAX(MATCH("*"&N$5&"*",TEXT($C6:$K6,"General"),0))),"-",INDEX($C$5:$K$5,,MAX(MATCH("*"&N$5&"*",TEXT($C6:$K6,"General"),0))))


Now Copy N6 across to O6:p6

Now Copy N6:p6 down to O7:p25


Enjoy
 
Good morning, Hui...thank you for this...I will try it and do some testing and let you know how it works....tlak to you later...thanks again
 
http://www.speedyshare.com/files/28208199/hlookup_for_hui.xls


password is muhafadakuzu


Hi Hui....I forgot to think about doing AM and PM jobs....if I have something like 123, 123-AM, 123-PM then the code you gave me doesn't quite work b/c the -AM or the -PM confuses the code where it is set up to MATCH "*"&n$5&"*"....the wildcards see the -AM and the -PM and put the wrong persons name in.....I uploaded a sample and jhave higjlighted the problem in yellow if you wish to look at it again? Thank you.
 
http://www.speedyshare.com/files/28228717/hlookup_for_hui_2_textgeneral.xls


password is tuzuxubodike


Hi again Hui...I have been working on this and I think I have a work-around....I replicate the worksheet (shown in my attached sample as yellow) into a sand colored copy (that will be hidden), and use SUBSTITUTE to remove the ***........then, the data moves to the blue worksheet that uses your formulas....everything seems to work fine, except that if the job is numbered 123, it doesn't seem to be recognized by your formula. I tried switching 123 to another number (eg: 234) and it works; if I switch it to something like 1105 (anything that starts with 1) it doesn't work. Is there something about the number 1 that doesn't work with the formula that you gave me? I attached a sample showing you what I mean. Thank you for any help.
 
Hi Hui...I think that I have figured out what is happening here...I forgot to 'label' the jobs as '123 etc....as soon as I changed 123 to '123 (in col n5) everything worked perfectly.....thank you for your help and I apologize for my mistake in setting this up...I will do more tests and let you know how it is working. Thanks again
 
Back
Top