Need help w/VLookup formula

[Stormy4757]

I have attached my spreadsheet for your review. The first tab is called “Data” and has six columns. Column A “Trancode” is the column I want to search on and find on the second tab called “AMR wTran Code”. When it finds the trancode, I want it to return the value in Column A of that line to the Data tab, Column E titled "Access Model". So the answer for this one would be “AM_ICS_TC_PAN_0016_UPD”. I want to create the formula on the “Data” tab in the E Column titled “Access Model”. Also, be aware, that I am still building the Data Tab, so a lot of them will not have matches yet. I have been trying to use Vlookup, but it hasn’t worked and then have found tutorials on MATCH and INDEX. I’ve tried using these and I can’t get a formula to work, but I am thinking one of them should. Any ideas?

[bgoree09]

Good morning,

Put this in E2 and drag down:

=INDEX('AMR wTran Code'!A:A,MATCH(Data!A2,'AMR wTran Code'!F:F,0))

Also, I may be able to help a little with index match. Index finds the answer in a range based on an intersection of row # and column # (we use match for this). In this case there is only one column the answer could be in (so it's simplified), but the format is something like:

index(**answer column**,match(**what you want to find**,**Column where you want to find it**,0 [zero means exact match] )). So, it will find the trancode in the list of trancodes, and return the corresponding value from the "answer column."

Best of luck,

[amit4blog]

It's really good.

[Stormy4757]

Thank you for your help. I like the way you explained the formula. I am going to copy that to my OneNote Excel tips. Where did you learn your advanced Excel skills? Can you recommend any schooling, tutorials, etc.? I have access to SkillSoft courses but they are so basic, they don't go into detail.

[bgoree09]

Hello again,

Most of what I've learned in excel came from trial and error. Microsoft has decent resources for explaining the formulas, but not necessarily for telling you which one you need. Usually a descriptive google search will put you in a good place to start and then narrow it down from there. As always, if that doesn't do the trick there is someone lurking here that can help 95% of the time. I've seen lots of solutions that I'm in awe of . I check the forums here and see if I can solve the problems to learn new things that I'm not exposed to regularly (and most of the time I can plow it back into something I was doing less efficiently). Hope this is helpful.

Best of luck,