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

T

Toria

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!
 
P

Pete_UK

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(#REF!),'[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
 
T

Toria

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 said:
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(#REF!),'[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 said:
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!
Click to expand...
Click to expand...
 
P

Pete_UK

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 said:
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 said:
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(#REF!),'[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 said:
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!
Click to expand...
Click to expand...
Click to expand...
 
P

pshepard

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"&MATCH(A1,Query_for_Analysis_Decks!AE:AE,0)))

Thanks,
Peggy
 
T

Toria

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 said:
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 said:
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 said:
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(#REF!),'[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


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!
Click to expand...
Click to expand...
Click to expand...
 
T

Toria

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 said:
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"&MATCH(A1,Query_for_Analysis_Decks!AE:AE,0)))

Thanks,
Peggy

Toria said:
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!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup Results Problems 6
Need help with INDEX lookup 14
Need help with formula to search and compare data in different col 4
Simplify formula 5
Lookup Formula help 8
Help: vlookup from multiple sheets and spreadhseets 1
Need help with this "StringConcat" formula 1
Complex Lookup Formula 3

Top