Question re Vlookup function within Excel.

Hi,

Can the following be done.

I have the following formula in Sheet1 of my workbook.

=VLOOKUP(B6,Sheet2!A2:BC499,5,)

When cell A2 of Sheet2 is populated with the name John and cell E2 contains 'ABC' the formula works ok, but when cell A3 is populated with the same name, but cell E3 has 'DEF' it won't display the text 'DEF'. Can this be done so that each time I enter the same name in column A with different data in column E it populates correctly.

Many thanks.

Thanks for your reply Ed.

I think vlookup is the wrong function too, but wasn't too sure and have only really used that formula in the past.

The reason I am asking the question is because I have created a spreadsheet that contains 4 sheets. The first sheet is my front page or basically where my vlookup results will be displayed. I have various sections within sheet1 that contains more than one row and my other sheet (sheet2) contains the data. But this sheet was getting quite big and a bit long and winded to update. I managed to source info relating to a userform that would enable me to enter the data quicker, but it populates per row rather than per column.

Thanks.

Hi 007juk,

It sounds like you have the right kind of structure by separating the data from the summary.

If you can post a an example of what you have and what you want to achieve I am sure that either myself or one of the others on the forum will be able to help.


Regards

Ed

Ed,

Please find attached file.

Basically I want Sheet1 - cells B11 & B12 to be populated with the data entered on Sheet2 - E3 & E4 and if I select different person it does the same. Hopefully I can then apply to other ranges etc.

I would like to keep Sheet2 limited to 10 columns. For info Haven't applied userform yet, but If I can sort this problem then that's what I will do.


Many thanks.

Hi,

From looking at your form I would say that ACCESS would be a better medium to use.

Excel is essentially a number cruncher and what you want to do is sort data by various criteria.

Ecxel is not good at looking for multiple instances of the same data key and then extracting all relevant records.

What you want may be possible with some clever VB coding - but I think a database solutuin would be easier and better.

Regards

Ed

Thanks for getting back to me, unfortunately we don't have access only excel. The document I'm creating is for ease of reference, so that anyone can use it and find the info.

Would access have the same function though, i.e. a similar function to vlookup.

Thanks again.

Access is a different beast altogether!

It is what is called a relational database - it is rather like a set of filing cabinets, with drawers and files linked by key data, so that given e.g. a name you can pull out the any or all of the information relating to that name as required.

If you can only use excel then I think the best way would be to rearrange your data so that you could use filters to pull out the information you need.

Ed