Can't find the right lookup formula for this

[Guest]

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.

 

[Niek Otten]

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

 

[Domenic]

Try...

=INDEX($C$1:$H$1,MATCH(10,INDEX($C$2:$G$4,MATCH("Site
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!

 

[Max]

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(Sheet1!$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,(Sheet1!$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 ..

 

[Guest]

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