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.
Vlookup, Hlookup, Match, Index
- Thread starter dmcnab
- Start date
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
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
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
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
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
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
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
5 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
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
5 to textie: '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?
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 O66
Now Copy N66 down to O725
Enjoy
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
6Now Copy N6
6 down to O725Enjoy
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.
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.
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