| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index, match, multiple IFs query
I have the following individual functions
=INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
Please let us know the cell range of ONE TWO ...etc; refers to..and if
possible elaborate a bit more about how your data is arranged -- Jacob (MVP - Excel) "zx6roo" wrote: I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
Will the value in F7 always appear exclusively in one of the ranges, or
could it appear in more than one? "zx6roo" wrote in message ... I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
F7 can be in any of the ranges and column 7 is exclusive to each range
"Steve Dunn" wrote: Will the value in F7 always appear exclusively in one of the ranges, or could it appear in more than one? "zx6roo" wrote in message ... I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
The data on sheet two is arranged in as a table.
Range ONE is B4 to B8 Range TWO is C4 to C8 And so on. F7 can be anything in any of those ranges. What I want is for a lookup to see what is in F7, look at the table, find whic range it is in and then give me the number that is located in column 5 of that range. "Jacob Skaria" wrote: Please let us know the cell range of ONE TWO ...etc; refers to..and if possible elaborate a bit more about how your data is arranged -- Jacob (MVP - Excel) "zx6roo" wrote: I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
sorry I meant column 5 not 7
"Steve Dunn" wrote: Will the value in F7 always appear exclusively in one of the ranges, or could it appear in more than one? "zx6roo" wrote in message ... I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
Hi,
You could try this =INDEX(Sheet2!$D$3:$H$7,SUMPRODUCT((B4:C8=F7)*ROW( B4:B8))-ROW($B$3),5) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zx6roo" wrote in message ... I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
Hi, no that didn't work.
The cells on sheet two B3:H7 are all text except column 5 which is a number. Sheet one F7 will be the text from anywhere within the sheet two B3:H7area (except column 5). Each row within the table area has a specific number - so if the text matched B3 the number is 1, if the text matched H7 the number is 1, if the text matched B4 the number is 2. I want it to automatically match the text in the table then look at the number in column 5 from the row (which I gave the range names to) and show me that number in the cell on sheet one. Did I explain that cleary? I can attach an example if needed. "Ashish Mathur" wrote: Hi, You could try this =INDEX(Sheet2!$D$3:$H$7,SUMPRODUCT((B4:C8=F7)*ROW( B4:B8))-ROW($B$3),5) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zx6roo" wrote in message ... I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
Hi,
slight adjustments to Ashish's solution, to suit your sheets: =INDEX(Sheet2!$B$3:$H$7,SUMPRODUCT((Sheet2!$B$3:$H $7=$F$7)*(ROW(Sheet2!$B$3:$H$7)-CELL("row",Sheet2!$B$3:$H$7)+1)),4) "zx6roo" wrote in message ... Hi, no that didn't work. The cells on sheet two B3:H7 are all text except column 5 which is a number. Sheet one F7 will be the text from anywhere within the sheet two B3:H7area (except column 5). Each row within the table area has a specific number - so if the text matched B3 the number is 1, if the text matched H7 the number is 1, if the text matched B4 the number is 2. I want it to automatically match the text in the table then look at the number in column 5 from the row (which I gave the range names to) and show me that number in the cell on sheet one. Did I explain that cleary? I can attach an example if needed. "Ashish Mathur" wrote: Hi, You could try this =INDEX(Sheet2!$D$3:$H$7,SUMPRODUCT((B4:C8=F7)*ROW( B4:B8))-ROW($B$3),5) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zx6roo" wrote in message ... I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index, match, multiple IFs query
Hi,
You may mail me the file at ask(at)ashishmathur(dot)com -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zx6roo" wrote in message ... Hi, no that didn't work. The cells on sheet two B3:H7 are all text except column 5 which is a number. Sheet one F7 will be the text from anywhere within the sheet two B3:H7area (except column 5). Each row within the table area has a specific number - so if the text matched B3 the number is 1, if the text matched H7 the number is 1, if the text matched B4 the number is 2. I want it to automatically match the text in the table then look at the number in column 5 from the row (which I gave the range names to) and show me that number in the cell on sheet one. Did I explain that cleary? I can attach an example if needed. "Ashish Mathur" wrote: Hi, You could try this =INDEX(Sheet2!$D$3:$H$7,SUMPRODUCT((B4:C8=F7)*ROW( B4:B8))-ROW($B$3),5) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "zx6roo" wrote in message ... I have the following individual functions =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,ONE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,TWO,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,THREE,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FOUR,0),5) =INDEX(Sheet2!$D$3:$H$7,MATCH($F7,FIVE,0),5) I need to combine them all in the same cell so IF F7 matches data in ranges ONE, TWO, ETC it will return whatever is in column 5. Ive done some searching on the net and also looked at Microsofts help on this. I can see how to create a multiple function if I want it to be ONE & TWO but not ONE or TWO. Ive tried various combinations of functions but a little stuck. Any help is much appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
| SUMPRODUCT or INDEX/MATCH for multiple conditions and multiple rec | Excel Discussion (Misc queries) | |||
| if, Isnumber, Match, &index query | Excel Discussion (Misc queries) | |||
| index and match query | Excel Worksheet Functions | |||
| INDEX AND MATCH QUERY ? | Excel Worksheet Functions | |||
Linear Mode
