Combined INDEX and MATCH formula will not work - pls help!

[Postlki1]

Hi folks,

Even though I have used this formulas before, I am stuck today.

- Basically I have a table range I2:J891 where column I would have employee names (old data) and column J has matching ID numbers.
- Then I have data in column A2:A401, even here employee names (updated data).
- Column C2:C401 is empty, but is supposed to show the coresponding ID numbers.

So, in order to get the correct ID number in cell c2 (and so on), excel will have to lookup the name in A2, go through all names in I2:I891 and return the matching employee ID as defined in J2:J891.

Maybe it is a rather a combination of LOOKUP and MATCH? I am not sure though.

Thanks for your help!

Cheers,
Kim

[benishiryo]

hi Kim. something like:
=IFERROR(VLOOKUP(A2,$I$2:$J$891,2,0),"")

or:
=IFERROR(VLOOKUP(A2,$I$2:$J$891,2,0),"Not Found")

if it doesnt work, do upload an Excel file & identify which is wrong, what should it be & why

[Postlki1]

Copy of Employee list for Kim_HR_20130506_excelforum.xlsmCopy of Employee list for Kim_HR_20130506_excelforum.xlsm

Hi again,

I am not sure if I actually uploaded something. Hope it worked. The formula you gave me does not di the trick.


I rearanged the columns I and J to A and B, just in case this would have been the reason for the misfuntion.

Thanks for helping me!

/Kim

[:) Sixthsense :)]

The benishiryo suggested formula unable to retrieve the result since your column-A values have extra spaces, just place the cursor in A2 cell and press F2 to check it.

In E2 Cell - Array Formula - Should be entered with CTRL+SHIFT+ENTER.

Please Login or Register  to view this content.

Drag it down..

[Postlki1]

Hi Sixthsense,

Thanks for the reply. However, I still get the "Not found" in every cell.

I checked the cell A2 with as suggested, and I see what you mean. There is empty space behind every name. Maybe if we could adjust the formula in a way that excel just looks up the beginning of a cell (eg. the first 8 letters) because the names more or less differ anyhow.

Could that work?

Thanks,
Kim

[Postlki1]

btw, I just realized that my data in column A is poor data as not all cells follow the same logic (last name, first name). However, cell A3 and A4 look as they should and get the return "Not found" as well.

I will quickly redo the data in cell A, so whatever formula we will use in the end will work,