Vlookup & hlookup??

[sbartmasser]

Good day.. I am trying to look up an item in a row on tab one and then look at the top row in serveral columns in tab two and bring back the data. The top row in each column might change monthly so I don't want to formula to find the column and then bring back the data. Below is an example:

Data- Tab 2
[TABLE="width: 364"]
<COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3865" width=101><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2892" width=75><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2508" width=65><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3276" width=85><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3763" width=98><COL style="WIDTH: 44pt" width=59><TBODY>[TR]
[TD="class: xl66, width: 101, bgcolor: transparent"]CusipDescription[/TD]
[TD="class: xl66, width: 75, bgcolor: transparent"]BookYield[/TD]
[TD="class: xl66, width: 65, bgcolor: transparent"]DatedDate[/TD]
[TD="class: xl66, width: 85, bgcolor: transparent"]BegBookValue[/TD]
[TD="class: xl66, width: 98, bgcolor: transparent"]BegMarketValue[/TD]
[TD="class: xl66, width: 59, bgcolor: transparent"]BegParValue[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]BOFA[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1.95[/TD]
[TD="class: xl68, bgcolor: white, align: right"]3/26/2010[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]200000[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]200758[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]200000[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]BANK OF WEST[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1.8[/TD]
[TD="class: xl68, bgcolor: white, align: right"]3/3/2010[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]200000[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]200549.4[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]200000[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]WELLS FARGO[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2.700110395[/TD]
[TD="class: xl68, bgcolor: white, align: right"]6/30/2010[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]606834.18[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]603225[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]500000[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Tab 1[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]CusipDescription[/TD]
[TD="class: xl66, bgcolor: transparent"]BegBookValue[/TD]
[TD="class: xl66, bgcolor: transparent"]DatedDate[/TD]
[TD="class: xl66, bgcolor: transparent"]BookYield[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]BOFA[/TD]
[TD="class: xl69, bgcolor: #ffc000"] 200,000.00 [/TD]
[TD="class: xl70, bgcolor: #ffc000, align: right"]3/26/2010[/TD]
[TD="class: xl71, bgcolor: #ffc000, align: right"]1.95[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]BANK OF WEST[/TD]
[TD="class: xl69, bgcolor: #ffc000"] 200,000.00 [/TD]
[TD="class: xl70, bgcolor: #ffc000, align: right"]3/3/2010[/TD]
[TD="class: xl71, bgcolor: #ffc000, align: right"]1.8[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"]WELLS FARGO[/TD]
[TD="class: xl69, bgcolor: #ffc000"] 606,834.18 [/TD]
[TD="class: xl70, bgcolor: #ffc000, align: right"]6/30/2010[/TD]
[TD="class: xl71, bgcolor: #ffc000, align: right"]2.700110395[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Any help would be greatly appreciated..
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]

[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Brian from Maui]

Try...

=INDEX($B$2:$E$8,MATCH(F1,$A$2:$A$8,0),MATCH(F2,$B$1:$E$1,0))

Where $B$2:$E$8 houses data to return
Where F1 and F2 are the criteria
Where $A$2:$A$8 and $B$1:$E$1 are row and column headers

 

[Russell Hauf]

I would agree with Brian, but if you tweak it a little you can drag it down and across to all of your cells.

=INDEX('Tab2'!$A$1:$J$10,MATCH('Tab1'!$A2,'Tab2'!$A$1:$A$10,0),MATCH('Tab1'!B$1,'Tab2'!$A$1:$J$1,0))

Note: Sheets are named Tab1, Tab2; Data table in Tab2 goes from A1 to J10; and use the row- and column-absolute references as appropriate ($A2, B$1 in my example).

 

[sbartmasser]

Thank you both for your quick response. I had heard about the Index / Match, but have never used it. This worked perfectly...and now I have a new tool in my Excel tool belt.

Sharon