After checking if a cell value exists in a column,

[knesl69]

After checking if a cell value exists in a column, I need to get the value of the cell next to the matching cell. For instance, I check if the value in cell A1 exists in column B through to column i, and assuming it matches B5, then I want the value in cell C5.


Example of my data.

[pre]

A      B      C        D         E         F         G        H        I       J 

1      0      BLANK    0         BLANK     1         C         0      BLANK     C
FORMULA - ANSWER HERE (SHOULD BE 1)    

2      1      A        0         BLANK     0         BLANK     0      BLANK      A
FORMULA - ANSWER HERE (SHOULD BE 1)

[/pre]
thank you for your help in advance Mark

 

[jason]

not sure if i totally understand:


you want to check if the value of A2 matches the values in B2:I2?? what is in B5?


maybe a better example/layout would help?

 

[Faseeh]

Hi knesl69,


As far as i have understood your problem, you want to Look up a value in a column and then fetch a corresponding row-value from a certain column, with your data like this in A1:J3,

[pre]

A	B	C	D	E	F	G	H	I	J
1	0	-	0	-	1	C	0	-	C
2	1	A	0	-	0	-	0	-	A

[/pre]

you can safely use VLOOKUP() for this task.


Regards,

 

[Luke M]

=INDEX(B2:J2,MATCH(A2,B2:J2,0)+1)


Do note that this formula will return a 0 for both cells that originally contained a 0 or were blank. It also doesn't have any error trapping in it, so if the value in col A is not found in cols B:J, this will give the #N/A! error.