Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for a number in a table and return data of a specific cell
This is the setup:
Table 1 Column 1 Column 2 Date1 Date1 Date2 Date2 A C E B Table 2 String1 Column A Column B E Task: For cell in Table 2 column A, find if "E" is present in Column 1 of Table 1, then read Date 1 into the cell, otherwise leave blank. Thank you and I hope that you can help me with this one. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for a number in a table and return data of a specific cell
try this:
=IF(ISERROR(MATCH(C18,$A:$A,0)0),"",A$2) A2 where the date is, assuming its location does not change C18 where E is if it doesn't work give me more detail. Thanks- "Karaman" wrote: This is the setup: Table 1 Column 1 Column 2 Date1 Date1 Date2 Date2 A C E B Table 2 String1 Column A Column B E Task: For cell in Table 2 column A, find if "E" is present in Column 1 of Table 1, then read Date 1 into the cell, otherwise leave blank. Thank you and I hope that you can help me with this one. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for a number in a table and return data of a specific cell
I presume we can ignore the stuff under Column 2 in Table 1 completely?
This is set up for all being on a single sheet, but would adapt easily to multiple sheet as I presume you're dealing with multiple sheets. It is very dependent on the layout of your data in the tables, since it's not conducive as is for either a VLOOKUP or HLOOKUP. A 1 Date1 2 Date2 3 A 4 E down in A13 I put the "E" and in B13 I put this formula: =IF(ISNA(MATCH(A13,A1:A4,0)),"",OFFSET(A1,ROW(A4)-MATCH(A13,A1:A4),0)) This is dependent on the "E" being looked for always being at the bottom of the table (A4 in this case) and the data being desired for return to be at the top of it. Nikki has provided another possible solution. But for both of our solutions, the layout (relative position of data) of the table is kind of critical. In point of fact if you are always going to look at stuff in row 4 and pick up stuff from row 1 you could do a very simple =IF(A13=A4,A1,"") again, in my example, I put the stand-alone E down in A13. "Karaman" wrote: This is the setup: Table 1 Column 1 Column 2 Date1 Date1 Date2 Date2 A C E B Table 2 String1 Column A Column B E Task: For cell in Table 2 column A, find if "E" is present in Column 1 of Table 1, then read Date 1 into the cell, otherwise leave blank. Thank you and I hope that you can help me with this one. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for a number in a table and return data of a specific c
Thank you very much for the assistance.
"JLatham" wrote: I presume we can ignore the stuff under Column 2 in Table 1 completely? This is set up for all being on a single sheet, but would adapt easily to multiple sheet as I presume you're dealing with multiple sheets. It is very dependent on the layout of your data in the tables, since it's not conducive as is for either a VLOOKUP or HLOOKUP. A 1 Date1 2 Date2 3 A 4 E down in A13 I put the "E" and in B13 I put this formula: =IF(ISNA(MATCH(A13,A1:A4,0)),"",OFFSET(A1,ROW(A4)-MATCH(A13,A1:A4),0)) This is dependent on the "E" being looked for always being at the bottom of the table (A4 in this case) and the data being desired for return to be at the top of it. Nikki has provided another possible solution. But for both of our solutions, the layout (relative position of data) of the table is kind of critical. In point of fact if you are always going to look at stuff in row 4 and pick up stuff from row 1 you could do a very simple =IF(A13=A4,A1,"") again, in my example, I put the stand-alone E down in A13. "Karaman" wrote: This is the setup: Table 1 Column 1 Column 2 Date1 Date1 Date2 Date2 A C E B Table 2 String1 Column A Column B E Task: For cell in Table 2 column A, find if "E" is present in Column 1 of Table 1, then read Date 1 into the cell, otherwise leave blank. Thank you and I hope that you can help me with this one. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for a number in a table and return data of a specific c
Thank you very much for the assistance.
"Nikki" wrote: try this: =IF(ISERROR(MATCH(C18,$A:$A,0)0),"",A$2) A2 where the date is, assuming its location does not change C18 where E is if it doesn't work give me more detail. Thanks- "Karaman" wrote: This is the setup: Table 1 Column 1 Column 2 Date1 Date1 Date2 Date2 A C E B Table 2 String1 Column A Column B E Task: For cell in Table 2 column A, find if "E" is present in Column 1 of Table 1, then read Date 1 into the cell, otherwise leave blank. Thank you and I hope that you can help me with this one. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Data in two seperate Spreadsheets | Excel Worksheet Functions | |||
VLOOKUP CONDITION TO RETURN BLANK CELL WHERE LOOKUP VALUE IS NOT IN TABLE ARRAY | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |