how to get Hlookup value only in specified cell

[genetist]

Hi to all,
I have data in 2 sheets like this,
sheet1
SX1 SX2 SX3 SX4 SX5
AA ZZ AA GG -/-
in sheet2 i have data like this
SX1 SX2 SX3 SX4 SX5
AA AA AA GG TT
i am doing Hlookup to get value in only columns containing ZZ or -/- not in another columns, my aim is even if i drag my formula from 1st column to last column i should get lookup value only in these in the columns containing ZZ or -/-.
my expected results will be
sheet1
SX1 SX2 SX3 SX4 SX5
AA AA AA GG TT
How can i get this value as per my expectations is Hlookup and match function together will work?
i need help really to solve this problem because my data is so big and i can not do it manually searching for ZZ or -/-.
any help would be appreciated
Thanks in advance
Genetist

 

[mohan.pandey87]

You can use it like this:

=IF(A2 = "-/-",HLOOKUP(A1,$G$1:$K$2,2,FALSE),A2)

 

[genetist]

Dear Mohan,
Thank you very much for your reply and for some reason if i dragged that formula giving same value across all the columns.

 

[mohan.pandey87]

Try this:


=IF(A1="","",IF(A2="-/-",HLOOKUP(A1,$A$8:$E$9,2,FALSE),A2))

 

[genetist]

Dear mohan,
Its working but if dragged it its giving same value like before

 

[kevatarvind]

hi something like below if you need something else then pls upload some sample data

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	SX1	SX2	SX3	SX4	SX5		SX1	SX2	SX3	SX4	SX5
2	AA	ZZ	AA	GG	-/-		AA	AA	AA	GG	TT
3	ZZ	AA	AA	-/-	GG		AA	AA	AA	GG	GG
Sheet1

Cell Formulas
Range	Formula
G2	=IF(OR(A2="ZZ",A2="-/-"),HLOOKUP(G$1,Sheet2!$A$1:$E$2,2,0),A2)

Excel 2007
	A	B	C	D	E
1	SX1	SX2	SX3	SX4	SX5
2	AA	AA	AA	GG	TT
Sheet2

 

[genetist]

hi something like below if you need something else then pls upload some sample data

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K
1	SX1	SX2	SX3	SX4	SX5		SX1	SX2	SX3	SX4	SX5
2	AA	ZZ	AA	GG	-/-		AA	AA	AA	GG	TT
3	ZZ	AA	AA	-/-	GG		AA	AA	AA	GG	GG

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet1​

Worksheet Formulas Cell Formula G2 =IF(OR(A2="ZZ",A2="-/-"),HLOOKUP(G$1,Sheet2!$A$1:$E$2,2,0),A2) <THEAD> </THEAD><TBODY> </TBODY>

<TBODY>
</TBODY>

Excel 2007

	A	B	C	D	E
1	SX1	SX2	SX3	SX4	SX5
2	AA	AA	AA	GG	TT

<COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Dear kevataravind,
Thank you very much for your formula and its working. i want to know how to use this formula A2 itself instead of in column G?
anyway thanks lot for your help

 

[kevatarvind]

i understood what you want to say but when how the formula will know that it cell has old value is ZZ or -/- so thats why you have to use like i have given or another way is

or macro can do this