Updating a cell based on another cells value

**joeyga** · 01-19-2012, 12:06 AM

Hi folks,

New job forcing me to use excel and under some stress hence my first post - please excuse my ignorance!

I have two worksheets with over 1000 rows:
Worksheet 1: Has 1000 values in Column A and 1000 corresponding values in Column B.
Worksheet 2: Has 400 with some of the same values found in Column A of worksheet 1 but no values in Column B

I need to do:
When the value in A1 (worksheet 2) = value in any cell in Column A (Worksheet 1)
take the value in column B for that same row (Worksheet 1)
and populate B1 (worksheet 2) with that same value

Any help would be much appreciated.
Thanks,
Joe

**bebo021999** · 01-19-2012, 12:11 AM

Try this in B2:

Please Login or Register  to view this content.

Copy downwards

**TMS** · 01-19-2012, 12:12 AM

Use VLOOKUP

=VLOOKUP(A1,WS1!A:B,2,FALSE)

Regards, TMS

**TMS** · 05-04-2013, 02:59 PM

Thanks for the rep

**TMS** · 05-04-2013, 07:11 PM

@Moderator(s)/Admin: I got positive rep from the OP on this thread and it is marked Solved.

However, I have received negative rep from Alex H, who has not been a contributor to the thread, with the comment, "Solution does not work".

Please can one of you take issue with Alex H about forum etiquette and behaviour and, ideally, reverse the negative rep.

Thanks, TMS

**TMS** · 05-05-2013, 06:08 PM

For clarification: Alex H has pointed out that my solution "did not work" in the sample workbook.

It's a while now, but I suspect this was a generic response, possibly using my iPad, and I may not have tried to open the sample workbook.

For completeness, the working solution would be:

Cell B2 formula: =VLOOKUP(A2,Worksheet1!A:B,2,FALSE) ... copied down.

To cater for missing values in Excel 2003 (from profile), it would be:

=IF(ISNA(VLOOKUP(A2,Worksheet1!A:B,2,FALSE)),"",VLOOKUP(A2,Worksheet1!A:B,2,FALSE))

And, to cater for missing entries in Excel 2007 and above (from sample file extension).

These formulae would be equivalent to that provided by bebo021999 but using full column address ranges.

Alex H has now balanced the rep.

Regards, TMS