|
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! |
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! |
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! |
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! |
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! |
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! |
| All times are GMT +1. The time now is 06:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com