How can I look up the value in the next cell?

[jackjones123]

Hi,

I have a set of data with a merged header cell, laid out like this:


---------------------------Merged header cell-----------------------------------
[TABLE="width: 500"]
<tbody>[TR]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to return Value 2, using the header cell as the lookup value. I've tried using HLOOKUP, but it returns Value 1. Is there a way I can return Value 2 instead?

Thanks!

 

[jorismoerings]

hI,

You've posted this question with the hope of getting an answer you can use.
Unfortunately you gave us not enough information to go on and by doing that, made it impossible for us to answer your question in a way you can actually use the answer.

My top tip today:
Look at the forum rules and guidelines, the links are provided in my signature below and by doing that you'll get some helpful guidance on what information you should include in your question to create the best opportunity for us to help you.

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:112.16px;" /><col style="width:135.92px;" /><col style="width:133.07px;" /><col style="width:114.06px;" /><col style="width:26.61px;" /><col style="width:26.61px;" /><col style="width:164.44px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td colspan="2" style="text-align:center; ">Merged header cell</td><td colspan="2" style="text-align:center; ">Other header cell</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Value1</td><td >Value2</td><td >Value3</td><td >Value4</td><td > </td><td > </td><td >Value2</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=INDEX(A1:D2,2,MATCH("Merged header cell",A1:D1,0)+1)</td></tr></table></td></tr></table>

 

[jackjones123]

Try this

	A	B	C	D	E	F	G
Value1	Value2	Value3	Value4			Value2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:112.16px;"><col style="width:135.92px;"><col style="width:133.07px;"><col style="width:114.06px;"><col style="width:26.61px;"><col style="width:26.61px;"><col style="width:164.44px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="colspan: 2, align: center"]Merged header cell[/TD]
[TD="colspan: 2, align: center"]Other header cell[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

</tbody>

Cell Formula G2 =INDEX(A1:D2,2,MATCH("Merged header cell",A1:D1,0)+1) <tbody> </tbody>

<tbody>
</tbody>

Sorry, it's taken me a couple of weeks to get back to this project. Thank you very much, that has worked perfectly!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.