Comparing columns and returning value

Closed
surajkumarraju Posts 1 Registration date Tuesday October 12, 2010 Status Member Last seen October 12, 2010 - Oct 12, 2010 at 09:25 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Oct 14, 2010 at 10:38 AM
Hello,

I had similer proble which was discussed at

https://ccm.net/forum/affich-100231-comparing-columns-and-returning-value

but with little change.

My data is

Sheet1

Column A Column B
1 a
2 e
3 b
4 c
5 d
6 f

and Sheet2
Column A Column B
a formule
b
c
d
e
f
g
h
i

what i want in column B of sheet2 is, compare column A of sheet 2 with column B of sheet1 and put a corresponding value of column A of sheet 1
example:

Column A Column B
a 1
b 3
c 4
d 5
e 2
f 6
g 0
h 0
i 0

g, h and i = 0 as there is no match for it in sheet 1.

Thanks in advonce

Suraj.

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 14, 2010 at 10:38 AM
Hi Suraj,

A condition for VLOOKUP is that the value that you want to look up is in the left most column of the table you want to look in. So you will need to rearrange your data. You can also get creative and make up column C the same as column A and and then hide column C or change the textcolor to white.

To get a 0 instead of an error message use this formula structure:
=IF(ISERROR(VLOOKUP(A1,sheet1!A1:B6,2)),0,VLOOKUP(A1,sheet1!A1:B6,2))

Best regards,
Trowa
0