Vlookup help?

[verno74]

Sorry if this has been asked before but I'm not even really sure how to ask what formula I need help with, fairly new to this but now a few of the basics. I'm trying to build a template for the purpose of billing. In A1 there's "part#" A2 "serial#" A3 "tech#" and A4 "description". B1,2,&3 I have validated drop down lists to choose from, but here's where it gets tricky, how do I get which ever I choose (a1,2 or 3 to to return info in b4? I can write a simple vlookup,to use 1 column at a time but not utilize all 3. Only 1 column would be selected at a time. I'm hope this makes sense. Sorry if Not.

[mehmetcik]

I would use a three cell lookup.

Sample attached.

enter cis into cell A2 and press tab
enter ana in B2 and press tab
click on the list box.


This is just something for you to consider.

Paste your sample spreadsheet with detailed requirements and I will customise something for you.

[vichopalacios]

@ verno74

It is not clear what you want to put in cell B4 and under what condition, and from where....
please explain better

[verno74]

I would like to select either a part # serial # or tech# and have the description return in b4

@ verno74

It is not clear what you want to put in cell B4 and under what condition, and from where....
please explain better

[humdingaling]

see attached solution i made your problem

Formula in b4 is
this solution does not require CSE

Please Login or Register  to view this content.

there are alternatives which do require CSE...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

essentially the scoped formula

checks for more how many items are entered
if with COUNTA(B1:B3)<=1
if 0...does into the primary formula which will error out and give "Product not found"
if greater than 1..ie 2 or 3...then more than one product selected( i should of probably wrote entered)

the primary formula is this

Please Login or Register  to view this content.

which at its core is a index/match formula

breaking it down
the index bit is simply looking at column L...description down the row 500 (extend as required)

Please Login or Register  to view this content.

SUMPRODUCT((ISBLANK(B1:B3)=FALSE)*ROW(B1:B3)) and SUMPRODUCT(MAX((B1:B3<>"")*(ROW(B1:B3))) do the same thing, i put both in so you could get the flavour of it
essentially it returns the row number (1,2 or 3) of what was typed in to lookup

if you type something in row 1 then the indirect will return B1 etc

the other piece of the puzzle the offset
so starting at H1, it will offset columns based on the sumproduct number...ie if you type in part# it will offset 1 column Right
etc

500 is the length..extend to your needs

[verno74]

Thanks guys I'll be sure to try these

[verno74]

How did you attach the file?

[FDibbins]

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[verno74]

Heres a screenshot of the spreadsheet I'm working on. I have drop down lists under Serial #, Part# & RDS as well and would like the selected column to return the information under the Description column. The information is on other tabs, I can get us VLOOKUP at a basic level. As in it works if I'm only wanting to use just a single column for the reference but not all three. [ATTACH]424243

[humdingaling]

this is a different setup to your original post however the same principals apply

attach a santised sample sheet on how your data is setup

[verno74]

Here is a sample sheet

[humdingaling]

cannot use the offset method with the way your data is setup
cannot use the CSE method with all your merged cells

Formula that goes into G19

Please Login or Register  to view this content.

principle same as above except the sumproduct goes across instead of down

[verno74]

That worked so awesome! Thank you for taking the time to assist me with that formula.