Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Question... LookUP | Excel Discussion (Misc queries) | |||
Polynimial trandline formula | Charts and Charting in Excel | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |