| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help getting first column data based on criteria?
Hi there- there seem to be quite a few of these posts, but I can't seem to
get it. I have the following: Ref No Object Color 123 Pen Red 345 Pen Green 678 Pencil Red I'm trying to get the "Ref No" data inserted into a cell if both the object and color are what I want (for instance, I want "345" returned if I type in "pen" and "green" when a table is searched). Any thoughts? Many thanks in advance! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help getting first column data based on criteria?
Perhaps one of these methods:
Using your example in A1:C4, AND... F1: pen F2: Green If the Ref No is ALWAYS NUMERIC F3: =SUMPRODUCT((B2:B4=F1)*(C2:C4=F2)*(A2:A4)) If the Ref No may be text F3: =OFFSET(A1,SUMPRODUCT((B2:B4&C2:C4=F1&F2)*ROW(A2:A 4)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi there- there seem to be quite a few of these posts, but I can't seem to get it. I have the following: Ref No Object Color 123 Pen Red 345 Pen Green 678 Pencil Red I'm trying to get the "Ref No" data inserted into a cell if both the object and color are what I want (for instance, I want "345" returned if I type in "pen" and "green" when a table is searched). Any thoughts? Many thanks in advance! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help getting first column data based on criteria?
Hi Ron- Thanks so much for your response! I'm using alpha-numeric data, so I
tried your second method. However, it's pulling up the reference number for the row beneath the row that the data is on. Any ideas? Many thanks again! "Ron Coderre" wrote: Perhaps one of these methods: Using your example in A1:C4, AND... F1: pen F2: Green If the Ref No is ALWAYS NUMERIC F3: =SUMPRODUCT((B2:B4=F1)*(C2:C4=F2)*(A2:A4)) If the Ref No may be text F3: =OFFSET(A1,SUMPRODUCT((B2:B4&C2:C4=F1&F2)*ROW(A2:A 4)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi there- there seem to be quite a few of these posts, but I can't seem to get it. I have the following: Ref No Object Color 123 Pen Red 345 Pen Green 678 Pencil Red I'm trying to get the "Ref No" data inserted into a cell if both the object and color are what I want (for instance, I want "345" returned if I type in "pen" and "green" when a table is searched). Any thoughts? Many thanks in advance! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help getting first column data based on criteria?
Well.....I guess I could have checked my formula before I posted it :\
Here's an alternative that works (and is easier to decypher): F3: =INDEX(A1:A4,MAX(SUMPRODUCT((B1:B4&C1:C4=F1&F2)*RO W(A1:A4)),1),1) Does that help? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi Ron- Thanks so much for your response! I'm using alpha-numeric data, so I tried your second method. However, it's pulling up the reference number for the row beneath the row that the data is on. Any ideas? Many thanks again! "Ron Coderre" wrote: Perhaps one of these methods: Using your example in A1:C4, AND... F1: pen F2: Green If the Ref No is ALWAYS NUMERIC F3: =SUMPRODUCT((B2:B4=F1)*(C2:C4=F2)*(A2:A4)) If the Ref No may be text F3: =OFFSET(A1,SUMPRODUCT((B2:B4&C2:C4=F1&F2)*ROW(A2:A 4)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi there- there seem to be quite a few of these posts, but I can't seem to get it. I have the following: Ref No Object Color 123 Pen Red 345 Pen Green 678 Pencil Red I'm trying to get the "Ref No" data inserted into a cell if both the object and color are what I want (for instance, I want "345" returned if I type in "pen" and "green" when a table is searched). Any thoughts? Many thanks in advance! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help getting first column data based on criteria?
Brillian! Ron- this works perfectly! Thank you so much! I really
appreciate it! "Ron Coderre" wrote: Well.....I guess I could have checked my formula before I posted it :\ Here's an alternative that works (and is easier to decypher): F3: =INDEX(A1:A4,MAX(SUMPRODUCT((B1:B4&C1:C4=F1&F2)*RO W(A1:A4)),1),1) Does that help? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi Ron- Thanks so much for your response! I'm using alpha-numeric data, so I tried your second method. However, it's pulling up the reference number for the row beneath the row that the data is on. Any ideas? Many thanks again! "Ron Coderre" wrote: Perhaps one of these methods: Using your example in A1:C4, AND... F1: pen F2: Green If the Ref No is ALWAYS NUMERIC F3: =SUMPRODUCT((B2:B4=F1)*(C2:C4=F2)*(A2:A4)) If the Ref No may be text F3: =OFFSET(A1,SUMPRODUCT((B2:B4&C2:C4=F1&F2)*ROW(A2:A 4)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi there- there seem to be quite a few of these posts, but I can't seem to get it. I have the following: Ref No Object Color 123 Pen Red 345 Pen Green 678 Pencil Red I'm trying to get the "Ref No" data inserted into a cell if both the object and color are what I want (for instance, I want "345" returned if I type in "pen" and "green" when a table is searched). Any thoughts? Many thanks in advance! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help getting first column data based on criteria?
You're very welcome....Thanks for the feedback. I'm glad that worked for you.
*********** Regards, Ron XL2002, WinXP "Tami" wrote: Brillian! Ron- this works perfectly! Thank you so much! I really appreciate it! "Ron Coderre" wrote: Well.....I guess I could have checked my formula before I posted it :\ Here's an alternative that works (and is easier to decypher): F3: =INDEX(A1:A4,MAX(SUMPRODUCT((B1:B4&C1:C4=F1&F2)*RO W(A1:A4)),1),1) Does that help? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi Ron- Thanks so much for your response! I'm using alpha-numeric data, so I tried your second method. However, it's pulling up the reference number for the row beneath the row that the data is on. Any ideas? Many thanks again! "Ron Coderre" wrote: Perhaps one of these methods: Using your example in A1:C4, AND... F1: pen F2: Green If the Ref No is ALWAYS NUMERIC F3: =SUMPRODUCT((B2:B4=F1)*(C2:C4=F2)*(A2:A4)) If the Ref No may be text F3: =OFFSET(A1,SUMPRODUCT((B2:B4&C2:C4=F1&F2)*ROW(A2:A 4)),0) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Tami" wrote: Hi there- there seem to be quite a few of these posts, but I can't seem to get it. I have the following: Ref No Object Color 123 Pen Red 345 Pen Green 678 Pencil Red I'm trying to get the "Ref No" data inserted into a cell if both the object and color are what I want (for instance, I want "345" returned if I type in "pen" and "green" when a table is searched). Any thoughts? Many thanks in advance! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional format based on data from another column | Excel Discussion (Misc queries) | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Sort pages? | Excel Discussion (Misc queries) | |||
| SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
| Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions | |||
Linear Mode
