Need Help with Lookup Formula that Also Puts in Text Where Matchin
 

Hello,

I have two different spreadsheets. I'm trying to write a formula that says
if the ID in A1 of the first spreadsheet matches anywhwere in column AE in
the second spreadsheet, put in the text from column G of the second
spreadsheet. This is what I came up with, but it's not putting in the text
from column G of that particular row where there was a match:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,0))

Many thanks in advance!

Need Help with Lookup Formula that Also Puts in Text Where Matchin
 

You will need a MATCH within the INDEX part of the formula, like this:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,MATCH(A10&"_"&ROW(#R EF!),'[Query for
Analysis Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)))

BUT, you do have #REF in the ROW function, so you will need to correct that
in two places before this will work (presumably you deleted the row that it
referred to).

Hope this helps.

Pete


"Toria" wrote in message
...
Hello,

I have two different spreadsheets. I'm trying to write a formula that
says
if the ID in A1 of the first spreadsheet matches anywhwere in column AE in
the second spreadsheet, put in the text from column G of the second
spreadsheet. This is what I came up with, but it's not putting in the
text
from column G of that particular row where there was a match:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for
Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,0))

Many thanks in advance!

Need Help with Lookup Formula that Also Puts in Text Where Mat
 

This is where I get confused. I'm not sure where or why ROW comes in. The
match could be on any row. Where the match is, that is the row that pulls
from column G on spreadsheet 2 (Query for Analysis Decks). Thank you. I
think I'm very close!

"Pete_UK" wrote:

You will need a MATCH within the INDEX part of the formula, like this:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,MATCH(A10&"_"&ROW(#R EF!),'[Query for
Analysis Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)))

BUT, you do have #REF in the ROW function, so you will need to correct that
in two places before this will work (presumably you deleted the row that it
referred to).

Hope this helps.

Pete


"Toria" wrote in message
...
Hello,

I have two different spreadsheets. I'm trying to write a formula that
says
if the ID in A1 of the first spreadsheet matches anywhwere in column AE in
the second spreadsheet, put in the text from column G of the second
spreadsheet. This is what I came up with, but it's not putting in the
text
from column G of that particular row where there was a match:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for
Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,0))

Many thanks in advance!

Need Help with Lookup Formula that Also Puts in Text Where Mat
 

You are trying to match using A10&"_"&ROW(something) with column AE. ROW(x)
can only return numbers like 1, 2, 3 etc, so if A10 contains, say, abc, then
you are looking for abc_1, or abc_2, or abc_3 etc.

Without more knowledge of what you have in that other file, I can't give you
a more-definitive answer. Where did you get that formula from?

Pete

"Toria" wrote in message
...
This is where I get confused. I'm not sure where or why ROW comes in.
The
match could be on any row. Where the match is, that is the row that pulls
from column G on spreadsheet 2 (Query for Analysis Decks). Thank you. I
think I'm very close!

"Pete_UK" wrote:

You will need a MATCH within the INDEX part of the formula, like this:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for
Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,MATCH(A10&"_"&ROW(#R EF!),'[Query
for
Analysis Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)))

BUT, you do have #REF in the ROW function, so you will need to correct
that
in two places before this will work (presumably you deleted the row that
it
referred to).

Hope this helps.

Pete


"Toria" wrote in message
...
Hello,

I have two different spreadsheets. I'm trying to write a formula that
says
if the ID in A1 of the first spreadsheet matches anywhwere in column AE
in
the second spreadsheet, put in the text from column G of the second
spreadsheet. This is what I came up with, but it's not putting in the
text
from column G of that particular row where there was a match:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for
Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,0))

Many thanks in advance!

Need Help with Lookup Formula that Also Puts in Text Where Matchin
 

Hi Toria,

Let me know if this works for you.

=IF(ISNA(MATCH(A1,Query_for_Analysis_Decks!AE:AE,0 ))=TRUE,"",INDIRECT("Query_for_Analysis_Decks!G"&M ATCH(A1,Query_for_Analysis_Decks!AE:AE,0)))

Thanks,
Peggy

"Toria" wrote:

Hello,

I have two different spreadsheets. I'm trying to write a formula that says
if the ID in A1 of the first spreadsheet matches anywhwere in column AE in
the second spreadsheet, put in the text from column G of the second
spreadsheet. This is what I came up with, but it's not putting in the text
from column G of that particular row where there was a match:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,0))

Many thanks in advance!

Need Help with Lookup Formula that Also Puts in Text Where Mat
 

Hi Pete,

I don't think I need to use the ROW function at all. I got the formula just
from looking around on this message board. Every time I took ROW out, I
would get a blank response. I guess I got desperate. I just can't figure out
how to perform this function.

"Pete_UK" wrote:

You are trying to match using A10&"_"&ROW(something) with column AE. ROW(x)
can only return numbers like 1, 2, 3 etc, so if A10 contains, say, abc, then
you are looking for abc_1, or abc_2, or abc_3 etc.

Without more knowledge of what you have in that other file, I can't give you
a more-definitive answer. Where did you get that formula from?

Pete

"Toria" wrote in message
...
This is where I get confused. I'm not sure where or why ROW comes in.
The
match could be on any row. Where the match is, that is the row that pulls
from column G on spreadsheet 2 (Query for Analysis Decks). Thank you. I
think I'm very close!

"Pete_UK" wrote:

You will need a MATCH within the INDEX part of the formula, like this:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for
Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,MATCH(A10&"_"&ROW(#R EF!),'[Query
for
Analysis Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)))

BUT, you do have #REF in the ROW function, so you will need to correct
that
in two places before this will work (presumably you deleted the row that
it
referred to).

Hope this helps.

Pete


"Toria" wrote in message
...
Hello,

I have two different spreadsheets. I'm trying to write a formula that
says
if the ID in A1 of the first spreadsheet matches anywhwere in column AE
in
the second spreadsheet, put in the text from column G of the second
spreadsheet. This is what I came up with, but it's not putting in the
text
from column G of that particular row where there was a match:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for
Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,0))

Many thanks in advance!

Need Help with Lookup Formula that Also Puts in Text Where Mat
 

Peggy,

I get an error. Sometimes REF and sometimes NAME. I think the problem may
be with this part of the formula: "Query_for_Analysis_Decks!G". This is just
a guess, but if you can help, I would really appreciate it!!

"pshepard" wrote:

Hi Toria,

Let me know if this works for you.

=IF(ISNA(MATCH(A1,Query_for_Analysis_Decks!AE:AE,0 ))=TRUE,"",INDIRECT("Query_for_Analysis_Decks!G"&M ATCH(A1,Query_for_Analysis_Decks!AE:AE,0)))

Thanks,
Peggy

"Toria" wrote:

Hello,

I have two different spreadsheets. I'm trying to write a formula that says
if the ID in A1 of the first spreadsheet matches anywhwere in column AE in
the second spreadsheet, put in the text from column G of the second
spreadsheet. This is what I came up with, but it's not putting in the text
from column G of that particular row where there was a match:

=IF(ISNA(MATCH(A10&"_"&ROW(#REF!),'[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!AE:AE,0)),"",INDEX('[Query for Analysis
Decks.xls]Query_for_Analysis_Decks'!G:G,0))

Many thanks in advance!