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.

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.
>

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.
>

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.
> >

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.
> >