Don't know how to use Lookup/Match/Index or a combination for this result

Using Excel 2003 on XP.

I'm not even sure if this is possible.

I have a worksheet (Sheet1) which is used to load bulk data into another application so I can only have values from row 10 down which are relevant to the upload. Above row 10 is not read by the upload program so additional data can be placed there for information purposes. Additional, unexpected data from Row 10 down will cause the upload to fail.

A table of company names and codes is contained on another worksheet Sheet2) in the same workbook. So, on Sheet2, in column A, there is a list of codes. In column B, is a corresponding list of names.

If I go to cell D10 on Sheet1 and type in the code: 1234, I would like the corresponding name, "The Company" to appear in cell D3 as a confirmation that the correct code has been typed. This is fine if I enter a VLOOKUP formula into D3 (=VLOOKUP(D10,Sheet2!A1:B3011,2,FALSE).

But, if I want to enter more codes into cells D11, D12, etc, is it possible to use another method to display the name of the selected cell in D3?

In a nutshell, regardless of which row from D10 down I type in a code, I would like to see just one name display in D3 depending on the selected cell further down. Even if this is possible, what would happen if a range was selected in column D?

TIA for any advice or assistance.

Originally Posted by Potoroo

Using Excel 2003 on XP.

I'm not even sure if this is possible.

I have a worksheet (Sheet1) which is used to load bulk data into another application so I can only have values from row 10 down which are relevant to the upload. Above row 10 is not read by the upload program so additional data can be placed there for information purposes. Additional, unexpected data from Row 10 down will cause the upload to fail.

A table of company names and codes is contained on another worksheet Sheet2) in the same workbook. So, on Sheet2, in column A, there is a list of codes. In column B, is a corresponding list of names.

If I go to cell D10 on Sheet1 and type in the code: 1234, I would like the corresponding name, "The Company" to appear in cell D3 as a confirmation that the correct code has been typed. This is fine if I enter a VLOOKUP formula into D3 (=VLOOKUP(D10,Sheet2!A1:B3011,2,FALSE).

But, if I want to enter more codes into cells D11, D12, etc, is it possible to use another method to display the name of the selected cell in D3?

In a nutshell, regardless of which row from D10 down I type in a code, I would like to see just one name display in D3 depending on the selected cell further down. Even if this is possible, what would happen if a range was selected in column D?

TIA for any advice or assistance.

You seem to be unsure of what you want in D3, hence it is difficult to make a suggestion.

You can have the lookup of a cell, or the lookups of a few cells (using lookup & " " & lookup " " & lookup etc) but you would need a larger formula in D3 to accomodate all the cells that you might need.

If you mean that you want D3 to show the results of a Lookup using the currently selected cell (or the last known selected cell) in the range D10:D65536 that is possible, perhaps a little unwise, but using VB code with a Worksheet_Change event, possible.

If you select a range the item should then be ignored.

hth
---

Hello Bryan,

Thank you for the suggestion. With over 3000 possible results when typing a code into the appropriate column, I thought it would be good to see verification that the correct code has been selected by displaying the corresponding name in a cell which is readily visible to the user.

I'll play around with your suggestion and see what I can come up with.

Thanks, again, for your time.