| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index / lookup / match / text formula | Excel Discussion (Misc queries) | |||
| When copying formula down it puts in a day of the week | Excel Discussion (Misc queries) | |||
| sumif function with partially matchin sentences | Excel Discussion (Misc queries) | |||
| Macro puts #REF in VLOOKUP formula | Excel Worksheet Functions | |||
| create reference formula that looks through all sheets for matchin | Excel Worksheet Functions | |||
Linear Mode
