Comparing columns and returning value

Closed
surajkumarraju Posts 1 Registration date Tuesday 12 October 2010 Status Member Last seen 12 October 2010 - 12 Oct 2010 à 09:25
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 14 Oct 2010 à 10:38
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.

Related:

1 response

TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
14 Oct 2010 à 10:38
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