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