VLOOKUP using the current cell as the look up value

[alma1219]

I need to use the VLOOKUP function where the cell I am calling the function from contains the value I want to use as the Look Up Value. For example, in cell B2 I have an ID number and want to search for this number and return a Name, to replace the ID with the Name.

Is this possible, and if so, how?

Thanks

[thepdaoson]

To do this, you need data somewhere. For example,

A B C

1 Lookup Value (B1) Returned Value (C1)

2 No ID Name
3 1 001A Mr A
4 2 002B Mr B
5 3 003B Mr C

Type:

C1: = IF(ISNA(VLOOKUP(B1,B3:C10,2,0)),"ID is not valid",VLOOKUP(B1,B3:C10,2,0))
B1: Type the ID you want to look for, for example 002B, them you get Mr B in C1.

[alma1219]

Thanks for your reply. I have data in another worksheet.

So in Worksheet1 I have data range of A2:B100 (column A is ID number and column B is Name)
In Worksheet2 I have a column of ID numbers (C1) which I want to replace with Names.

I currently have in cell C1 (Worksheet2) =VLOOKUP(C1, Worksheet1!$A$2:$B$100, 2, FALSE) but this just returns 0.

[thepdaoson]

Sheet2: C1=VLOOKUP(Sheet1!A2,Sheet1!A2:B100,2,0) and pull down. I am not sure if it is done. If you want to resolve exactly your problem, you should upload your file.

[alma1219]

I have attached my spreadsheet of what I'm trying to do. Hopefully this helps explain my problem better. Thanks

[icestationzbra]

are you intending to put a value in the same cell as the formula that will use that value to derive another value?

[alma1219]

No, I just want to replace the ID number with the Name

[thepdaoson]

You can not use Sheet2: A2 as lookup value in this case. If you want State Names in column B of sheet1 appear in column A of sheet 2, you just type in A2 =VLOOKUP(Sheet1!A2, Sheet1!$A$2:$B$12, 2, FALSE) and pull down for others.

[alma1219]

I don't think this solution will work because the IDs in Sheet2 will vary and won't be in the same order as those from Sheet1.

Sheet1 will be used as a "base" set of data which won't change, and Sheet2 is always going to be different. There will be a list of ID numbers supplied (in random order) and I want to be able to easily replace each ID number with the Name.

Perhaps VLOOKUP isnt the best function to use?