Vlookups, look up value issues

[Rise]

First off, Hi! I originally went by the name of officespacer in woody's lounge and I saw I had a PM about this "new" board. After checking out, it was one of the best PMs I've ever gotten and thanks for inviting me! It definitely has the original lounge feel to it with some updated software and I need to give a quick to the guys who've put this great idea together. Thanks for taking the initiative

now down to business...

I have a vlookup that goes like this:
=VLOOKUP(C$3 & BAHTTEXT($B4), Inventory!A2:B136, 1, f)
where C3= "CA" and B4 = "1"

I basically made a grid that goes from CA -> CZ in a single row (X), and 1 -> 7 in a single column (Y). What I was hoping to do was make a grid that reflected inventory locations on actual racks at my place of work. So the vlookup references a sheet that has 2 columns, 1 with part number data, and 1 with rack location, looks like this:

Code: Select all

Rack Location	Part
CB1	CN11447
CB2	CN11312
CB3	C11093
CB3	C12648
CB4	CN7056
CB5	C12602
CC2	CN7476
CC3	CN11079
CC4	CN2101

And I'm hopiong to end up with this:

Code: Select all

	 CA	    CB	    CC	    CD	    CE       CF
1	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
2	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
3	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
4	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
5	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
6	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?
7	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?

obviously instead of #Name? I' m looking for the actual values from the other sheet. Why does what I'm doing not work? It looks like it doesn't like putting the CA & 1 together

-John

[HansV]

Hi John,

Welcome to Eileen's Lounge! I hope you'll enjoy our small new site. You'll see many familiar names.

Try this in C4:

=VLOOKUP(C$3&$B4,Inventory!$A$2:$B$136,2,FALSE)

Note that the 4th argument is FALSE, not f, and that I didn't use the BAHTTEXT function (I don't know why we'd need that).

You can fill this down, then right (or vice versa).

[Rise]

originally i had C$3&$B4 but it rejected it, probably because I had "f" in there instead of false... the really interested thing to me is that excel 2007 is what put the f in after I clicked on "False"!

Thanks Hans, you're as helpful as ever! :)