| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup for a value in multiple columns and return a result
Hi,
I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
Do you mean that the lookup_value could be in either columns B&C or F&G or
none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
Hi, thats correct.
Using lookup function i could only look up in B&C but couldnt give a condition that if not found in B&C, then look in F&G. "T. Valko" wrote: Do you mean that the lookup_value could be in either columns B&C or F&G or none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
Also, is the value to be returned a number or text?
Biff "T. Valko" wrote in message ... Do you mean that the lookup_value could be in either columns B&C or F&G or none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
The data to be returned is Text.
thanks "T. Valko" wrote: Also, is the value to be returned a number or text? Biff "T. Valko" wrote in message ... Do you mean that the lookup_value could be in either columns B&C or F&G or none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
may be we can start with this one...
Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows '=IF(COUNTIF(Sheet2!B1:C50,B2)0,INDEX((Sheet2!B1: D50),IF(NOT(ISNA(MATCH(B2,Sheet2!B1:B50,0))),MATCH (B2,Sheet2!B1:B50,0),IF(NOT(ISNA(MATCH(B2,Sheet2!C 1:C50,0))),MATCH(B2,Sheet2!C1:C50,0),#N/A)),3),IF(COUNTIF(Sheet2!G1:G50,B2)0,INDEX((Sheet 2!F1:H50),IF(NOT(ISNA(MATCH(B2,Sheet2!F1:F50,0))), MATCH(B2,Sheet2!F1:F50,0),IF(NOT(ISNA(MATCH(B2,She et2!G1:G50,0))),MATCH(B2,Sheet2!G1:G50,0),#N/A)),3),"NOT found")) regards, driller -- ***** birds of the same feather flock together.. "Ram" wrote: The data to be returned is Text. thanks "T. Valko" wrote: Also, is the value to be returned a number or text? Biff "T. Valko" wrote in message ... Do you mean that the lookup_value could be in either columns B&C or F&G or none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
Your formula doesn't work if the lookup_value is in column F.
Biff "driller" wrote in message ... may be we can start with this one... Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows '=IF(COUNTIF(Sheet2!B1:C50,B2)0,INDEX((Sheet2!B1: D50),IF(NOT(ISNA(MATCH(B2,Sheet2!B1:B50,0))),MATCH (B2,Sheet2!B1:B50,0),IF(NOT(ISNA(MATCH(B2,Sheet2!C 1:C50,0))),MATCH(B2,Sheet2!C1:C50,0),#N/A)),3),IF(COUNTIF(Sheet2!G1:G50,B2)0,INDEX((Sheet 2!F1:H50),IF(NOT(ISNA(MATCH(B2,Sheet2!F1:F50,0))), MATCH(B2,Sheet2!F1:F50,0),IF(NOT(ISNA(MATCH(B2,She et2!G1:G50,0))),MATCH(B2,Sheet2!G1:G50,0),#N/A)),3),"NOT found")) regards, driller -- ***** birds of the same feather flock together.. "Ram" wrote: The data to be returned is Text. thanks "T. Valko" wrote: Also, is the value to be returned a number or text? Biff "T. Valko" wrote in message ... Do you mean that the lookup_value could be in either columns B&C or F&G or none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
Try this array formula** :
=IF(COUNTIF(Sheet2!B2:C10,B2),INDEX(Sheet2!D2:D10, MATCH(TRUE,ISNUMBER(SEARCH(B2,Sheet2!B2:B10&"_"&Sh eet2!C2:C10)),0)),IF(COUNTIF(Sheet2!F2:G10,B2),IND EX(Sheet2!H2:H10,MATCH(TRUE,ISNUMBER(SEARCH(B2,She et2!F2:F10&"_"&Sheet2!G2:G10)),0)),"")) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Ram" wrote in message ... The data to be returned is Text. thanks "T. Valko" wrote: Also, is the value to be returned a number or text? Biff "T. Valko" wrote in message ... Do you mean that the lookup_value could be in either columns B&C or F&G or none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
lookup for a value in multiple columns and return a result
Sir Valko,
i've tried the formula and make a scenario on column f and g if i try to place the same lookup value on f30 and also the same lookup value on g20... the result will come from h20 and it will disregard the other result from h30. in my formula, under the same scenario, i bear the h30 result... maybe the OP is interested about priorities between column or row, firstly... i guess he likes the column to be prioritized...maybe i have misunderstood the look_up sequencing... regards, driller -- ***** birds of the same feather flock together.. "T. Valko" wrote: Try this array formula** : =IF(COUNTIF(Sheet2!B2:C10,B2),INDEX(Sheet2!D2:D10, MATCH(TRUE,ISNUMBER(SEARCH(B2,Sheet2!B2:B10&"_"&Sh eet2!C2:C10)),0)),IF(COUNTIF(Sheet2!F2:G10,B2),IND EX(Sheet2!H2:H10,MATCH(TRUE,ISNUMBER(SEARCH(B2,She et2!F2:F10&"_"&Sheet2!G2:G10)),0)),"")) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "Ram" wrote in message ... The data to be returned is Text. thanks "T. Valko" wrote: Also, is the value to be returned a number or text? Biff "T. Valko" wrote in message ... Do you mean that the lookup_value could be in either columns B&C or F&G or none? It's not just in either column B or column F? Biff "Ram" wrote in message ... Hi, I'll give you an idea of the file that i am working on Sheet 1: Cell B2 has numeric data ex: 39022 Sheet 2: Column B, C & D; F,G & H have data upto 50 rows Destination cell for the formula is Sheet1!B3 What i need the formula in Sheet!B3 to do: search for the value from Sheet1!B2 in Sheet2!B:C and if value exists then return value from column D; if value does not exist in B or D, then search in columns F & G, if exists then return value from H. If value does not exist in either B:C and F:G, then return blank It has been very challenging for me to solve this. I manage to look up using the look up function (vector), however i couldnt figure out, how to integrate the condition. Please let me know if you require more input from me. regards, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup 3 columns and return a result from another column | Excel Discussion (Misc queries) | |||
| BIFF - Help!! lookup numbers in multiple columns and return one nu | Excel Worksheet Functions | |||
| Lookup in Multiple Columns, Return Multiple Values | Excel Worksheet Functions | |||
| lookup value and return result in column to left | Excel Worksheet Functions | |||
| I need a Lookup to return more than 1 result | Excel Worksheet Functions | |||
Hybrid Mode
