Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bankscl
 
Posts: n/a
Default 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.

  #2   Report Post  
Niek Otten
 
Posts: n/a
Default

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.



  #3   Report Post  
Domenic
 
Posts: n/a
Default

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.

  #4   Report Post  
Max
 
Posts: n/a
Default

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.



  #5   Report Post  
bankscl
 
Posts: n/a
Default

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula Question... LookUP G Excel Discussion (Misc queries) 4 March 7th 05 02:38 AM
Polynimial trandline formula CLR Charts and Charting in Excel 9 February 7th 05 08:31 PM
Excel has a "Find Next" command but no "Find Previous" command. Michael Fitzpatrick Excel Discussion (Misc queries) 2 January 11th 05 12:45 AM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 02:27 AM
Formula Result Correct but value in the cell is wrong jac Excel Worksheet Functions 2 December 17th 04 09:05 PM


All times are GMT +1. The time now is 09:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"