VLookup Formula using two worksheets

**Stormy4757** · 12-26-2013, 11:26 AM

I need a formula to look up the Trancode on the "Data" tab and find it on the “AMR wTranCodes” tab and send me back the Access Model Name for that trancode. I am running the Vlookup from the “Data” tab. Right now what I have is:

=Vlookup(A2,”AMR wTran Codes’!A2:F11,’AMR wTranCodes’!A:A,)

As you can see it doesn’t work. Anyone? Below are snapshots of my worksheets. They are in the same document. Does the formatting of the cell have anything to do with it? Column E in the Data tab is General. Column F in the AMBR wTran Codes tab is Number.

Thanks for any help you can be.

**ChemistB** · 12-26-2013, 11:39 AM

You'll need to use LOOKUP or INDEX/MATCH. VLOOKUP only works if the value you are looking up is on the left side of your lookup range.
Try:
=INDEX('AMR wTran Codes'!A$2:A$1000, MATCH(A2, 'AMR wTran Codes'!F$2:F$1000,0))

However, very few of your transcodes from Data are in column F of AMR...

**Stormy4757** · 12-26-2013, 11:51 AM

Let me explain again. The first tab is the Data tab which contains the "Trancode" that I want to search for on the second tab (AMR wTran Codes) and find the Access Model name that correlates with that trancode. I want to bring that Access Model name back to the Data tab so that the boss can see that info on the Data tab. I tried your formula, it did not work. Anyone else?

**FDibbins** · 12-26-2013, 12:03 PM

What ChemistB is saying is that out of 6242 entries, only 1 360 transcoded in DATA are in the AMR sheet. I tested the formula suggested, and it works just fine.

I tried your formula, it did not work.

what didnt work? what answer were you expecting?

**Stormy4757** · 12-26-2013, 01:01 PM

I want to pull the Access Model Name from column A of the 2nd tab, "AMR...". This formula is pulling the trancode over. And yes, I know that all of the numbers in the DATA tab are not there. It is not fully built yet!

Let's go to line 5042 of the Data tab. The Transcode is 0016. We move over to the Column E to insert the formula. When inserting the formula, whatever we use, LOOKUP, MATCH, INDEX, we go to the AMR wTran Code tab, (take a look) and we see the Tran Code we are looking for on line 2. I want the result in Column A returned. I hope this makes sense. Maybe I haven't been making myself clear. IDK Anyway, I have pasted the formula provided and handtyped it in and the first thing it does is give me a pop up window that is titled "Update Values: AMR wTran Codes". It looks like the pop up window you get when you are opening a new document.

**ChemistB** · 12-26-2013, 01:10 PM

Here you go with the formula applied.
Row 5042 returns AM_ICS_TC_PAN_0016_UPD
Is this what you are looking for?

**Jonmo1** · 12-26-2013, 01:10 PM

It works for me (and you have been perfectly clear about what you want).

You can't put the formula Chemist posted into E5042 directly.
Because it would then be looking at A2 (not A5042)

You have to put it in E2, then Fill it down so the A2 incriments to A3 to A4 etc...

In E5042 the formula will look like
=INDEX('AMR wTran Codes'!A$2:A$1000, MATCH(A5042, 'AMR wTran Codes'!F$2:F$1000,0))

See here
EFStormy.xlsx

**Stormy4757** · 12-26-2013, 01:34 PM

ChemistB:
Yes, this is what I am looking for but when I tried your formula it did not work for me. I did paste it into the row two and copied it down. Don't know what happened. I will play with it to see why it did not work for me. I have saved your worksheet for my reference. I have studied it and it now makes sense to me. Not sure why it didn't work the first few times. Thank you! Thank you! Thank you!

Stormy

**Jonmo1** · 12-26-2013, 01:37 PM

Perhaps you have calculation set to manual in your book.

**Stormy4757** · 12-26-2013, 01:52 PM

Jonmo1,
I took a look and I have it set to auto. But thanks for your feedback. Since I have your attention, can either one of you recommend a course on excel that really goes into detail on this kind of stuff? I have access to Skillsoft courses online but you probably know they are so basic. I have watched some on YouTube. I am willing to spend the money if there is a good comprehensive one. Thanks again!

Stormy

**Jonmo1** · 12-26-2013, 01:56 PM

I've never been much of a 'book learner'
I learn best by trial and error.
So reading these excel forums was really the best educational tool for me.

**Stormy4757** · 12-26-2013, 02:01 PM

ok thanks!

**ChemistB** · 12-26-2013, 02:03 PM

Sorry, my story is the same as Jonmo's. I learned 90% of what I know about Excel on the forums and trying them out.

**FDibbins** · 12-26-2013, 02:52 PM

I guess that makes 3 of us then, most of what I know has come from here and from a "there must be an easier way of doing this" approach

VLookup Formula using two worksheets

LinkBack

Thread Tools

Rate This Thread

Display

VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: VLookup Formula using two worksheets

Re: SOLVED: VLookup Formula using two worksheets

Re: SOLVED: VLookup Formula using two worksheets

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dynamic Vlookup Formula to get information from different worksheets

I need an IF or a VLOOKUP formula to search and compare two worksheets in the same workboo

[SOLVED] VBA code for change vlookup formula to vlookup formula with ISNA

[SOLVED] Help Joining 3 worksheets using common fields using IF & VLOOKUP formula

[SOLVED] how to use 2 worksheets in one formula with INDIRECT & VLOOKUP

Bookmarks

Bookmarks

Posting Permissions