Hi everyone,
I've got a problem which I'm getting myself tied in knots with . I have a list of names and phone numbers on one sheet. On multiple other sheets, people will be putting in names, which then need to have the correct numbers inserted beside them. So far, so simple (Index/Match).
Here's the problem: the names and numbers are in cols A and B but in cols C and D are updates to the names and numbers. Sometimes the old name is still valid, with a new number; sometimes there's a new name but the old number is still valid; sometimes there's a new name and new number. The entries on the multiple sheets could be either the old or new names and I need to extract the correct number - either old or new as appropriate.
I could just put all the old names and old numbers into the new name/number columns and do a simple Index/Match that way, but then the changes tend to get lost (there's a LOT of names/numbers).
Here's what the name/number data looks like:.....A........B........C.........D
1..OldName...OldNo...NewName...NewNo
2...aaa.......111
3...bbb.......222....bbbnew
4...ccc.......333.............333000
5...ddd.......444....dddnew...444000
Here's what I want:.....F.........G
1..Name.......No
2..aaa.........111
3..bbbnew......222
4..ccc......333000
5..dddnew...444000
I've been trying various combinations of IFERROR, OR, ISERROR, some=0, etc, etc - but I can't get a formula which works for all combinations of OldName+OldNo, OldName+NewNo, NewName+OldNo and NewName+NewNo.
For example, this formula works for new numbers, but not with old numbers:
It returns:
0
0
333000
444000
Attached is an example file. It has named ranges for OldName, OldNo, NewName, NewNo, to make things a bit easier. If someone here can get his/her head round this and let me know what I'm missing, I'd greatly appreciate it.
Edit: Also, I can't change the structure of the multiple sheets where I need the correct numbers, nor can I easily add any other sheets (the file is used as a source to generate a telephone directory on another system which is very particular about what's there).
Bookmarks