HLOOKUP & VLOOKUP Combinded

[williamsabbie]

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 th
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 dat
searched.
The data cannot be amended so I have to stick with this layout.
Any ideas?
Thanks in advanc

 

[Don Guillett]

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

Instead, look in the help index for MATCH and then for INDEX>combine

 

[williamsabbie]

;1497757']On Feb 28, 9:45*am, williamsabbie <williamsabbie.
(e-mail address removed)> 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 th 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

Instead, look in the help index for MATCH and then for INDEX>combine

I have tried Index & Match combined, but the data changes rows week t
week, and when I use the match formula it will only look for data alon
the same line.
Vlookup only views the data in the first column and you have to specif
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 dat

 

[Don Guillett]

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

 

[zvkmpw]

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 A119, including the header row A11.

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,A11,0)-2,18,1),0),
MATCH($R$2,A11,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.