Lookup question....I feel so stupid

mapakunk · May 15th, 2002, 12:07 PM

OK I have
c D
4 Supplier CH NAME
5 mc00001 (where i want to put formula

The matching index is on Sheet 3 (a b)

I need to match the entry in c5
with the corresponding number in sheet 3's column a & b

I checked the old posts but could not find anything.
I've used help, the formula wizard, and etc. It does not come up with the exact match. I used lookup, and it returned the name before the line i needed. I've also tried to use ,False) and it returns an error

I've tried =lookup(c5,sheet3!a:a,Sheet3!b:b)
could someone please help me.

[ This Message was edited by: mapakunk on 2002-05-15 12:22 ]

Brian from Maui · May 15th, 2002, 12:36 PM

[ This Message was edited by: brian from maui on 2002-05-15 12:33 ]

mapakunk · May 15th, 2002, 01:22 PM

I cannot get it to work

I tried using the power tip

http://www.mrexcel.com/tip021.shtml

And that doesn't seem to do it either

I've moved my table to be between k5:l74

I'm just trying to put the CH NAME of the supplier code in the column to the right of the supplier code. I don't want to do this manually.

So the equation would sound like

If the cell in c5 is equal to a cell in k:k then d6 equals the cell to the right of the match in L:L

Maybe this will help,

If you can refresh my memory on how to do it, I'll post the sheet to the board....

shift F9 or something

invisigirl · May 15th, 2002, 01:36 PM

Never fear! Brian from Maui is a genius.

I think what you need to do is name your range on Sheet 3, and then put that in where Brian had the sheet reference listed.

So, let's say you named Sheet 3's range "sheet3." Then your formula would read (if I can remember where you were wanting to put the formula):

=VLOOKUP(A5,sheet3,2)

See if that helps. I used that very formula yesterday (with help from Brian!) and it worked great for me.

invisigirl · May 15th, 2002, 01:38 PM

Oops - it was C5. Sorry!

And I should probably explain that you would select the cells in the K area on sheet 3.

mapakunk · May 15th, 2002, 01:43 PM

Please forgive me if I sound frustrated, but I've been staring at this problem for HRS

The suppliers names are things like

MA00001
MA00002
etc

When it performs the lookup, I think it avoids the numeric portion on the end. So, any ideas on how it can find exact match of text & numbers together?

This is important because I am preparing a database of invoices that I can export to outlook. And seeing the real name of our supplier makes much more sense than al00001 as we have to put it in the system.

Not my idea on the supplier codes though...

Thank you

Brian from Maui · May 15th, 2002, 02:10 PM

Howzit,

Don't know what happened to my response but,

In sheet 3 column K - Suppliers code
Column L - Suppliers name

enter in d6
=vlookup(c5,sheet3!K5:l74,2)
sort columns K and L

watch out for extra spaces in your lookup table

If this doesn't work, you'll have to wait for Aladin, Mark W. or

to help you, because I ain't no genius but these guys are!

[ This Message was edited by: Brian from Maui on 2002-05-15 13:12 ]

[ This Message was edited by: Brian from Maui on 2002-05-15 22:08 ]

invisigirl · May 15th, 2002, 02:14 PM

Wow, that IS frustrating! I just duplicated (and expanded) your data, and it worked fine for me. The only thing I can think of is maybe the way the cells in your range are formatted. Mine defaulted to "general," and it worked for me, although I can't imagine how, even if they were formatted as text, your lookup would fail to read through the whole number.

Hmm. At least my post will bring it back to the top for a moment. Check the formatting, and if that doesn't work, maybe someone else will see this and reply with an answer.

Good luck!!!

Chris Davison · May 15th, 2002, 03:44 PM

mapakunk,

invisigirl's suggestion should work, although maybe in cell D5, not C5

Goto sheet 3 and name the 2 column range :

Highlight the range of your suppliers and CH Numbers

goto "insert"
then "names"
then "define"

name it (personally, I wouldn't use a name like "sheet" as that's something we refer to just about every few minutes in excel and this can be confusing - the same as naming files "menu" or naming tabs "lookup")

the "refers to" box should already have your absolute range in it as you highlighted it earlier.

click "add"

You've now named that range on sheet3. (Assume we called it "jinky")

Now back in Sheet1, the formula in D5, as invisigirl suggested, would be

=VLOOKUP(C5,jinky,2,0)

keep the C5 relative without any $$ anchoring and you should be able to copy it down the list.

(by the way, if you think that your list of stuff on Sheet3 will expand/contract over time, let us know, as we can set up the named range so that it too expands and contract with additions to or deletions from it, automatically, without the need to edit manually the size of the range)

_________________
Hope this helps,
Chris
(Excel '97, Windows ME)

[ This Message was edited by: Chris Davison on 2002-05-16 08:01 ]

mapakunk · May 15th, 2002, 09:55 PM

could it be because these items were imported from a text file that the way excel is reading them it is confused?? I don't understand why it will give me a close(1 or 2 lines away) match, but an exact match. It must be something so simple that is being overlooked by me. Oh well I'll keep looking, and maybe it'll get figured out sooner or later

thanks for all your help so far.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 15th, 2002, 12:07 PM	#1
mapakunk New Member Join Date: May 2002 Posts: 40	OK I have c D 4 Supplier CH NAME 5 mc00001 (where i want to put formula The matching index is on Sheet 3 (a b) I need to match the entry in c5 with the corresponding number in sheet 3's column a & b I checked the old posts but could not find anything. I've used help, the formula wizard, and etc. It does not come up with the exact match. I used lookup, and it returned the name before the line i needed. I've also tried to use ,False) and it returns an error I've tried =lookup(c5,sheet3!a:a,Sheet3!b:b) could someone please help me. [ This Message was edited by: mapakunk on 2002-05-15 12:22 ]

May 15th, 2002, 12:36 PM	#2
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	[ This Message was edited by: brian from maui on 2002-05-15 12:33 ]

May 15th, 2002, 01:22 PM	#3
mapakunk New Member Join Date: May 2002 Posts: 40	I cannot get it to work I tried using the power tip http://www.mrexcel.com/tip021.shtml And that doesn't seem to do it either I've moved my table to be between k5:l74 I'm just trying to put the CH NAME of the supplier code in the column to the right of the supplier code. I don't want to do this manually. So the equation would sound like If the cell in c5 is equal to a cell in k:k then d6 equals the cell to the right of the match in L:L Maybe this will help, If you can refresh my memory on how to do it, I'll post the sheet to the board.... shift F9 or something

May 15th, 2002, 01:36 PM	#4
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Never fear! Brian from Maui is a genius. I think what you need to do is name your range on Sheet 3, and then put that in where Brian had the sheet reference listed. So, let's say you named Sheet 3's range "sheet3." Then your formula would read (if I can remember where you were wanting to put the formula): =VLOOKUP(A5,sheet3,2) See if that helps. I used that very formula yesterday (with help from Brian!) and it worked great for me.

May 15th, 2002, 01:38 PM	#5
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Oops - it was C5. Sorry! And I should probably explain that you would select the cells in the K area on sheet 3.

May 15th, 2002, 01:43 PM	#6
mapakunk New Member Join Date: May 2002 Posts: 40	Please forgive me if I sound frustrated, but I've been staring at this problem for HRS The suppliers names are things like MA00001 MA00002 etc When it performs the lookup, I think it avoids the numeric portion on the end. So, any ideas on how it can find exact match of text & numbers together? This is important because I am preparing a database of invoices that I can export to outlook. And seeing the real name of our supplier makes much more sense than al00001 as we have to put it in the system. Not my idea on the supplier codes though... Thank you

May 15th, 2002, 02:10 PM	#7
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Howzit, Don't know what happened to my response but, In sheet 3 column K - Suppliers code Column L - Suppliers name enter in d6 =vlookup(c5,sheet3!K5:l74,2) sort columns K and L watch out for extra spaces in your lookup table If this doesn't work, you'll have to wait for Aladin, Mark W. or to help you, because I ain't no genius but these guys are! [ This Message was edited by: Brian from Maui on 2002-05-15 13:12 ] [ This Message was edited by: Brian from Maui on 2002-05-15 22:08 ]

May 15th, 2002, 02:14 PM	#8
invisigirl Board Regular Join Date: Mar 2002 Location: Oregon Posts: 130	Wow, that IS frustrating! I just duplicated (and expanded) your data, and it worked fine for me. The only thing I can think of is maybe the way the cells in your range are formatted. Mine defaulted to "general," and it worked for me, although I can't imagine how, even if they were formatted as text, your lookup would fail to read through the whole number. Hmm. At least my post will bring it back to the top for a moment. Check the formatting, and if that doesn't work, maybe someone else will see this and reply with an answer. Good luck!!!

May 15th, 2002, 03:44 PM	#9
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	mapakunk, invisigirl's suggestion should work, although maybe in cell D5, not C5 Goto sheet 3 and name the 2 column range : Highlight the range of your suppliers and CH Numbers goto "insert" then "names" then "define" name it (personally, I wouldn't use a name like "sheet" as that's something we refer to just about every few minutes in excel and this can be confusing - the same as naming files "menu" or naming tabs "lookup") the "refers to" box should already have your absolute range in it as you highlighted it earlier. click "add" You've now named that range on sheet3. (Assume we called it "jinky") Now back in Sheet1, the formula in D5, as invisigirl suggested, would be =VLOOKUP(C5,jinky,2,0) keep the C5 relative without any $$ anchoring and you should be able to copy it down the list. (by the way, if you think that your list of stuff on Sheet3 will expand/contract over time, let us know, as we can set up the named range so that it too expands and contract with additions to or deletions from it, automatically, without the need to edit manually the size of the range) _________________ Hope this helps, Chris (Excel '97, Windows ME) [ This Message was edited by: Chris Davison on 2002-05-16 08:01 ]

May 15th, 2002, 09:55 PM	#10
mapakunk New Member Join Date: May 2002 Posts: 40	could it be because these items were imported from a text file that the way excel is reading them it is confused?? I don't understand why it will give me a close(1 or 2 lines away) match, but an exact match. It must be something so simple that is being overlooked by me. Oh well I'll keep looking, and maybe it'll get figured out sooner or later thanks for all your help so far.