Hi Guys,
I'm trying to work out how to create a formula.
I need it to do a hlookup, and then a vlookup, but only in the particular range.
I want to lookup the date in row 1,
then the kid, bringing back the number in the next column.

So along the top will be ID Number | Date
Then the columns will have IDs in first column the a value in the next

Example- 201201 along 1:1. Then lookup C54321, should bring back 7

kid |201201
D12345 |1
C54321 |7
A6789 |5
B1234 |3
C54322 |1

But i only want it to search the 2 columns corresponding to the date searched.
The data cannot be amended so I have to stick with this layout.
Any ideas?
Thanks in advance

On Feb 28, 9:45*am, williamsabbie <williamsabbie.
wrote:
Hi Guys,
I'm trying to work out how to create a formula.
I need it to do a hlookup, and then a vlookup, but only in the
particular range.
I want to lookup the date in row 1,
then the kid, bringing back the number in the next column.

So along the top will be ID Number | Date
Then the columns will have IDs in first column the a value in the next

Example- 201201 along 1:1. Then lookup C54321, should bring back 7

kid * * * * * * * * * *|201201
D12345 *|1
C54321 *|7
A6789 * |5
B1234 * |3
C54322 *|1

But i only want it to search the 2 columns corresponding to the date
searched.
The data cannot be amended so I have to stick with this layout.
Any ideas?
Thanks in advance

--
williamsabbie

Instead, look in the help index for MATCH and then for INDEXcombine

I have tried Index & Match combined, but the data changes rows week to week, and when I use the match formula it will only look for data along the same line.
Vlookup only views the data in the first column and you have to specify which column the data will be in. I need it to lookup the data on row 1, then look up the ID only within the two columns with that date

On Tuesday, February 28, 2012 9:45:28 AM UTC-6, williamsabbie wrote:
Hi Guys,
I'm trying to work out how to create a formula.
I need it to do a hlookup, and then a vlookup, but only in the
particular range.
I want to lookup the date in row 1,
then the kid, bringing back the number in the next column.

So along the top will be ID Number | Date
Then the columns will have IDs in first column the a value in the next

Send file with complete explanation to dguillett1 @gmail.com






















Example- 201201 along 1:1. Then lookup C54321, should bring back 7

kid |201201
D12345 |1
C54321 |7
A6789 |5
B1234 |3
C54322 |1

But i only want it to search the 2 columns corresponding to the date
searched.
The data cannot be amended so I have to stick with this layout.
Any ideas?
Thanks in advance




--
williamsabbie

I have tried Index & Match combined, but the data changes rows week to
week, and when I use the match formula it will only look for data along
the same line.

If I understand correctly, there are pairs of kid/date columns side by side.

As an example using Excel 2003, there are eight pairs in A1:P19, including the header row A1:P1.

In R1 is the kid to be matched.

In R2 is the date to be matched.

Then this formula seems to work:

=OFFSET($A$1,
MATCH($R$1,OFFSET($A$1,1,MATCH($R$2,A1:P1,0)-2,18,1),0),
MATCH($R$2,A1:P1,0)-1)
If there can be more rows, add to the 18. If there can be more columns, change the P1's and move R1 & R2 someplace else.

To account for "no match" situations, you might want to try something like:
=IF(ISERROR(FormulaAbove), "", FormulaAbove)

Hope this helps getting started.