| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match values in two column and return value in
I want to look in two columns. I want to look in the first column for a cell
that matches the data in another cell. And with that value, I want to find a blank value in the second column. Once it finds the row with this value, I want to return the value in column G of this row. Example: Col A Col B Col G TBD 1 20000 TBD 1 X (This is where I want to look for the value TBD 1 in column A and blank in Col B, so it returns the value 20000 from column G) TBD 2 10000 TBD 2 X Can you help with a formula? Thank You, PW |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values in two column and return value in
Is the item you're looking for always the 1st item of that group? That's how
it looks in your posted sample. If that's the case try this: =INDEX(G2:G10,MATCH("TBD 1",A2:A10,0)) Another possibility: =SUMPRODUCT(--(A2:A10="TBD 1"),--(B2:B10=""),G2:G10) -- Biff Microsoft Excel MVP "PW" wrote in message ... I want to look in two columns. I want to look in the first column for a cell that matches the data in another cell. And with that value, I want to find a blank value in the second column. Once it finds the row with this value, I want to return the value in column G of this row. Example: Col A Col B Col G TBD 1 20000 TBD 1 X (This is where I want to look for the value TBD 1 in column A and blank in Col B, so it returns the value 20000 from column G) TBD 2 10000 TBD 2 X Can you help with a formula? Thank You, PW |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values in two column and return value in
It's not always the first item. However, I did get it to work with INDEX and
MATCH. Thank You! PW "T. Valko" wrote: Is the item you're looking for always the 1st item of that group? That's how it looks in your posted sample. If that's the case try this: =INDEX(G2:G10,MATCH("TBD 1",A2:A10,0)) Another possibility: =SUMPRODUCT(--(A2:A10="TBD 1"),--(B2:B10=""),G2:G10) -- Biff Microsoft Excel MVP "PW" wrote in message ... I want to look in two columns. I want to look in the first column for a cell that matches the data in another cell. And with that value, I want to find a blank value in the second column. Once it finds the row with this value, I want to return the value in column G of this row. Example: Col A Col B Col G TBD 1 20000 TBD 1 X (This is where I want to look for the value TBD 1 in column A and blank in Col B, so it returns the value 20000 from column G) TBD 2 10000 TBD 2 X Can you help with a formula? Thank You, PW |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match values in two column and return value in
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "PW" wrote in message ... It's not always the first item. However, I did get it to work with INDEX and MATCH. Thank You! PW "T. Valko" wrote: Is the item you're looking for always the 1st item of that group? That's how it looks in your posted sample. If that's the case try this: =INDEX(G2:G10,MATCH("TBD 1",A2:A10,0)) Another possibility: =SUMPRODUCT(--(A2:A10="TBD 1"),--(B2:B10=""),G2:G10) -- Biff Microsoft Excel MVP "PW" wrote in message ... I want to look in two columns. I want to look in the first column for a cell that matches the data in another cell. And with that value, I want to find a blank value in the second column. Once it finds the row with this value, I want to return the value in column G of this row. Example: Col A Col B Col G TBD 1 20000 TBD 1 X (This is where I want to look for the value TBD 1 in column A and blank in Col B, so it returns the value 20000 from column G) TBD 2 10000 TBD 2 X Can you help with a formula? Thank You, PW |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Search a column for values, return a value from adj column | Excel Worksheet Functions | |||
| Search, Match, And return corresponding column value | Excel Worksheet Functions | |||
| Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
| Match a cell to something in a column and return a YES | Excel Worksheet Functions | |||
| Index? Match? Function to sort and return value fr diff column in | Excel Worksheet Functions | |||
Linear Mode
