Can't find the right lookup formula for this
 

I have a spreadsheet like this:
Name Total week1 week2 week3 week4 week5 etc
Site A 5 5
Site B 10 10
Site C 12 12
(140 sites)
In order to feed my database I would like to have a formula that looks for
the week that contains the number from the "total" column and gives that week
as a result.
i.e. "Look for 10 in the Site B row and tell me what week it's under"
It seems like it should be so simple but I haven't been able to find a way
to make it work using Hlookup, vlookup, Index, or Match..
Any help is appreciated.

If your data is in columns A to G:

=INDEX($C$1:$G$1,1,MATCH(B2,C2:G2,0))


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"bankscl" wrote in message
...
I have a spreadsheet like this:
Name Total week1 week2 week3 week4 week5 etc
Site A 5 5
Site B 10 10
Site C 12 12
(140 sites)
In order to feed my database I would like to have a formula that looks for
the week that contains the number from the "total" column and gives that
week
as a result.
i.e. "Look for 10 in the Site B row and tell me what week it's under"
It seems like it should be so simple but I haven't been able to find a way
to make it work using Hlookup, vlookup, Index, or Match..
Any help is appreciated.

Try...

=INDEX($C$1:$H$1,MATCH(10,INDEX($C$2:$G$4,MATCH("S ite
B",$A$2:$A$4,0),0),0))

OR

=INDEX($C$1:$H$1,MATCH(B9,INDEX($C$2:$G$4,MATCH(A9 ,$A$2:$A$4,0),0),0))

....where B9 contains the number of interest, and A9 contains the site of
interest. Adjust the ranges accordingly.

Hope this helps!

In article ,
"bankscl" wrote:

I have a spreadsheet like this:
Name Total week1 week2 week3 week4 week5 etc
Site A 5 5
Site B 10 10
Site C 12 12
(140 sites)
In order to feed my database I would like to have a formula that looks for
the week that contains the number from the "total" column and gives that week
as a result.
i.e. "Look for 10 in the Site B row and tell me what week it's under"
It seems like it should be so simple but I haven't been able to find a way
to make it work using Hlookup, vlookup, Index, or Match..
Any help is appreciated.

One way to try ...

Assuming this table is
in Sheet1, cols A to G, data from row2 down

Name Total week1 week2 week3 week4 week5
Site A 5 5
Site B 10 10
Site C 12 12
(140 sites)

In Sheet2
---------
If A1 contains: Site B, B1 contains: 10

Put in the formula bar for C1:

=IF(OR(A1="",B1=""),"",IF(ISNA(MATCH(B1,OFFSET(She et1!$C$1:$G$1,MATCH(1,(She
et1!$A$2:$A$200=A1)*(Sheet1!$B$2:$B$200=B1),0),),0 )),"",INDEX(Sheet1!$C$1:$G
$1,MATCH(B1,OFFSET(Sheet1!$C$1:$G$1,MATCH(1,(Sheet 1!$A$2:$A$200=A1)*(Sheet1!
$B$2:$B$200=B1),0),),0))))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

C1 will return: week4
i.e. the week# from Sheet1 for the matched inputs in A1 and B1

Copy C1 down to return correspondingly
for other pairs of inputs in cols A and B

Adapt to suit ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"bankscl" wrote in message
...
I have a spreadsheet like this:
Name Total week1 week2 week3 week4 week5 etc
Site A 5 5
Site B 10 10
Site C 12 12
(140 sites)
In order to feed my database I would like to have a formula that looks for
the week that contains the number from the "total" column and gives that
week
as a result.
i.e. "Look for 10 in the Site B row and tell me what week it's under"
It seems like it should be so simple but I haven't been able to find a way
to make it work using Hlookup, vlookup, Index, or Match..
Any help is appreciated.

That did the trick! Thank you so much, beating my head against the wall was
my next step!

"Niek Otten" wrote:

If your data is in columns A to G:

=INDEX($C$1:$G$1,1,MATCH(B2,C2:G2,0))


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"bankscl" wrote in message
...
I have a spreadsheet like this:
Name Total week1 week2 week3 week4 week5 etc
Site A 5 5
Site B 10 10
Site C 12 12
(140 sites)
In order to feed my database I would like to have a formula that looks for
the week that contains the number from the "total" column and gives that
week
as a result.
i.e. "Look for 10 in the Site B row and tell me what week it's under"
It seems like it should be so simple but I haven't been able to find a way
to make it work using Hlookup, vlookup, Index, or Match..
Any help is appreciated.