Lookup Table 1

[stpmtp]

Hello,

I am having a difficult time with an excel spread sheet.

I have a main spread sheet and then others that contain data that I need to import into the main spread sheet
for example

On my main spread sheet I have
A B C
1 Item Size Color

2 001 2" red

3 001a 7/8" blue

4 001b 1/3" green

5 001c 1" orange


on another spreadsheet I have

A B
1 color code

2 red 1

3 blue 2

4 green 3

5 orange 4

I need to import "code" Into my main spread sheet.


Thanks in advance.

 

[stpmtp]

I forgot to post
the main spread sheet would look like

A B C D
1 Item Size Color Code

2 001 2" red 1

3 001a 7/8" blue 2

4 001b 1/3" green 3

5 001c 1" orange 4

 

[bluedragon2]

in D2 put = VLOOKUP(<sheet 2 name>!<table range>,C2,2,0)

Where <sheet 2 name> is the name of sheet 2 and the <table range> is the range of your table.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

sorry had it backwards:

=VLOOKUP(C2,<sheet 2 name>!<table range>,2,0)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[firefytr]

Hi there,

Take a look at the VLOOKUP function. Post back if you need more help.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[bluedragon2]

Also since you will want to copy this down, you will want to make the table referance absoulute. For example:

sheet2!A$1$:B$4$

and, you do not want the lookup value to be absolute: C2 with no $'s

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[stpmtp]

bluedragon2

so it would be something like this

=VLOOKUP(bo3,<colors.xls>!<B2:B87>,2,0)

 

[firefytr]

Leave out the < and > signs, like ...

=VLOOKUP(BO3,'[colors.xls]SheetNameHere'!$A$2:$B$87,2,0)

If colors.xls is your sheet name (not filename) then ...

=VLOOKUP(BO3,'colors.xls'!$A$2:$B$87,2,0)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[stpmtp]

I get the following #N/A

 

[firefytr]

That's because it can't find a match and with the 0 (zero) last syntax you're telling it to find an exact match. Check for the same value(s) in the left-most column of the lookup table (syntax 2) and ensure you have no leading/trailing spaces.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[stpmtp]

sorry, my mistake I got it.

Thank you so very, very much for all the help