Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 295

# @If @Vlookup ?????

Hi there:

I have a 123 spreadsheet with 2 lists, I need to extract from list B the names that are NOT in List A .

Any Ideas

Renee Webster
0
rwebsteris
• 6
• 3
1 Solution

Commented:
Table named LISTA
chg
dgfnh
dsb
fgf
fjhgk
fjm
fredwdlfj
retj

Make LISTA a named range two columns wide although column 2 will have nothing in it.

LISTB
cvbn
gfshfg
nbvn
retj
fgf
vb
vcxfg
xcbn

In the column after LISTB (assuming it's in column E)put the following formula:

@IF(@ISERR(@VLOOKUP(E1,\$LISTA,1)),C1,"")

copy it down the column and it will only list those items not in LISTA.

Hope that does what you want - please ask if my explanation is a bit cryptic!

0

Commented:
Apologies the formula should have been:

@IF(@ISERR(@VLOOKUP(E1,\$LISTA,1)),E1,"")

0

Commented:
By the way if you have names split into 2 column e.g. Renee in column A and Webster in column B it would be better to join them together by using a 3rd column with the following formula in it +A1&B1. With this formuala you will get ReneeWebster in the 3rd column. Then you can put that column into LISTA and use a similar approach to LISTB - so that you are comparing like with like. At least that way you will search for the complete name not just the given name nor just the family name.

hth
0

Author Commented:
Hi patrickkab,

Seems to work OK except that it returns "0".

Is there some way of having the name copied into column E.
0

Commented:
I have just re-tried it for myself at home and it does work. So I now need from you some information to be able to figure out what is happening and why it isn't working for you. Can you let me know:

1. The name of the range for the first set of data. You originally called it LISTA in the question

2. The column in which you have the second list - you originally called this LISTB - although it does not need a range name for my solution to work.

By the way for the @vlookup() to work you do need to sort LISTA in ascending order on the first column in the range LISTA.

The other thing is that the list of names you get using the @vlookup() will need to be copied and paste/special/values to get just the values and not the formulae.

It's 11:15pm here so if I don't get your response this evening I will have a look at it tomorrow.

Meanwhile I hope this helps.
0

Author Commented:
Hi patrickab;

thanks for getting back to me. I am at home now and do not have 123 on my home computer. I will take a closer look at it in the morning when I get to work. I am in the Toronto area. EST. I usually arive in the office @ 7:30 which if my calcs are correct would make it 12:30 your time.

Again thanks so much for the assistance.

Renee Webster
0

Commented:
Chere Renee,

That should give us a window of opportunity of about 5hrs to sort this out today. If you would like to send me a spreadsheet as an example (it can use dummy data) that might speed up the whole process. My email address is patrick.arnold-baker@jaeger.co.uk

Hope to hear from you soon.

Patrick
0

Author Commented:
Thanks ever so much for all your assistance.
0

Commented:
My pleasure - thanks for the A grade
0

## Featured Post

• 6
• 3
Tackle projects and never again get stuck behind a technical roadblock.