Using vlookup in Excel to match a Surname and First name and output unique ref number?

I have a spreadsheet which has 3 columns

Col A is a unique reference number Col B is the surname Col C is the firstname

In another worksheet I want to be able to type in a surname in cel开发者_JAVA技巧l A2, a firstname in cell A3 and have cell A1 automatically populated with the unique reference number.

Is this possible in Excel and if so could you give me an example of how to do it?

Thanks,

Greg

---

VLOOKUP requires that the data that you're looking up is in the first column and that the range is sorted by that column.

If that's not an issue, then you could shift your columns on your source sheet to the right and put in a new column containing something like =CONCATENATE(upper(C1)," ",upper(D1)) into the new column A - then use VLOOKUP against that column. (this is what Kaloyan said).

Otherwise you could put your concatenated, capitalised column and index on another (hidden) sheet and run your vlookup against that range.

---

Most straightforward way would be to combine the two names in another column like so: =upper(B1)&" "&upper(C1). Then you can do a vlookup on those values to get the unique ref #.