vlookup question

[jcavigli]

Hi,

First let me say, I never used vlookup before. If you kindly refer to the attached file, I will explain my problem.

1.) In column A I have department categories. For example, look at 'Analyst.' In DB#1 it has a specific name, in DB #2 it has a different name and in DB#3 it has yet a different name, etc.
2.) I would like the user to select a department entry from B12 on Worksheet1 and for it to match up with the DB#1 - DB#5 on Worksheet2.
3.) I pasted those values in for Analyst. Those would be the correct answer in that situation. This is just an example.

How can this be done using vlookup? Thank you in advance for any help.

[rylo]

G'day

Sheet1!B3: =VLOOKUP(B1,Sheet2!$A$1:$B$5,2,FALSE)
Copy across. You will have to change the entry in F1 from DB # 5 to DB #5 to match your table.

This isn't based on the data in B12. If you want to include B12 in your workings, then what are you going to do for the outputs for the rest when the category isn't Analyst? Do you want them to be blank? Where will the data for the other items be?

rylo

[jcavigli]

If you want to include B12 in your workings, then what are you going to do for the outputs for the rest when the category isn't Analyst? Do you want them to be blank? Where will the data for the other items be?

What I presented was a simplification of a problem I have. If I can do this, I can solve the larger problem at hand. As it is, it is hard to describe problems over the net and it is unfair for my to post the 'larger' problem and consume more of your time than I have to. This is why I did what I did. Again, I know this seems like an awkward request. But, if I can do this, I can solve the larger problem at hand. I just need help getting started.


Thank you for the rapid response, but this doesn't accomplish what I need. What I need is for the user to pick a department category like 'Analyst' in cell B12 of Worksheet1. After doing this, Worksheet2 should have this department category 'matched up' properly to DB #1 - DB#5. What you currently see on Worksheet2 is the correct answer I pasted in.

[Richard Buttrey]

Hi,

Try the following in B1 on sheet 2 and copied down.

Please Login or Register  to view this content.

HTH

[jcavigli]

Formula works perfectly. Any particular reason why vlookup wasn't used?

[jcavigli]

I can't seem to make this thing work if the position of the chart and drop down menu and output cells change. In the attached file, I changed the position, of all 3:
* Chart
* Selection Cell where you choose a professional type
* Output cell on Sheet 2

I used modified the code to this:

Please Login or Register  to view this content.

But I am getting the #Ref! error. Could someone give me a generic formula or show me how to modify this one then perhaps I can pick up the concept and know how to apply this stuff?

[Richard Buttrey]

Hi,

It works perfectly well for me with that formula and your adjusted ranges.

If however you want to generalise this, then as always you are better naming ranges and using the names in formulae. Hence create the following names.

On Sheet1
"Table" to be C25:I32
"Selection" to be D35
"TableRows" to be C25:C32

Now use the same formula but with names. i.e.

=INDEX(Table,MATCH(Selection,TableRows,FALSE),ROW()+1)

I've re-attached the workbook and am showing for reference the very original formula in column B of Sheet2, the revised formula for the re-positioned table in column C and the revised named range formula in column D

HTH

[jcavigli]

I see what you did on Sheet2 for columns C and D. I understand what you did there with the named ranges.

But, I would like the formulas to be put in H16:H21. It seems when I try to use those formulas you used in C and D on Sheet2 the #Ref! error pops up. If you view the attached, you will see this type of error in the range.

I can't figure out what is causing this. All I know is I'm not applying the concept properly.

My guess is that the row part must be modified to make it dynamic. For now, I just will use 2 for DB#1, 3 for DB#2, 4 for DB#3, etc. This will get rid of the errors. However, I know there must be a better way.

Please Login or Register  to view this content.

[Richard Buttrey]

Your guess is indeed correct. The original formula assumed that they would be entered against the DB1..DB2 etc labels which start on row 1. Now that you've moved the list to start in G16 the formula needs modifying.

Sticking with the good practice of using the named ranges as I indicated last time, also name the cell G16 "DBTop". Now you can used the modified formula:

=INDEX(Table,MATCH(Selection,TableRows,FALSE),ROW()+1-ROW(DBTop))

And this will work wherever you cut and paste the table or the DB1..DB2 etc list.

HTH

[jcavigli]

Finally!

The new formula works perfectly. I see how everything works now. I will save this example for future reference. I am sure I will need this again.

Thank you so much for your patience.

[Richard Buttrey]

Glad to have been of help, and thanks for the feedback.

Rgds,

R