Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
morning all.
I want to look through a range of cells and match two cells, then in a third cell place a specific value. I.e., something akin to the following. Sht1-column A sht1-ColumnB sht2-Column E sht2-ColumnF 12345 A 34258 ? 34258 B blank ? 56789 C 12345 ? For sht2-ColumnF I want to do a worksheet function that will match up the matching 5 digit numbers sht1-columnA and sht2-Column E, and then insert the correct letter to column F. Last week I'd tried something akin to: =if(A2=match('sht2'!F2,f1:f190,0),vlookup(......), "") and of course it failed. This morning I realized that it was because my match only returns the location if the response is true, and and N/A error if the value cannot be found. I need a Letter input in the F column IF the 5 digit number series matches, and blank if there's no 5 digit series in Column E. If this is not clear, please advise, and I'll try explaining it differently. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
Hi Steve
If I have understood you correctly, then =IF(E2="","",INDEX(Sheet1!B:B,MATCH(E2,Sheet1!A:A, 0))) -- Regards Roger Govier "SteveDB1" wrote in message ... morning all. I want to look through a range of cells and match two cells, then in a third cell place a specific value. I.e., something akin to the following. Sht1-column A sht1-ColumnB sht2-Column E sht2-ColumnF 12345 A 34258 ? 34258 B blank ? 56789 C 12345 ? For sht2-ColumnF I want to do a worksheet function that will match up the matching 5 digit numbers sht1-columnA and sht2-Column E, and then insert the correct letter to column F. Last week I'd tried something akin to: =if(A2=match('sht2'!F2,f1:f190,0),vlookup(......), "") and of course it failed. This morning I realized that it was because my match only returns the location if the response is true, and and N/A error if the value cannot be found. I need a Letter input in the F column IF the 5 digit number series matches, and blank if there's no 5 digit series in Column E. If this is not clear, please advise, and I'll try explaining it differently. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
Try it this way in F2 of sht2:
=IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",INDEX('sht1'!B :B,MATCH(E2,'sht1'! A:A,0))) then copy it down. You could replace the INDEX part with your VLOOKUP if you wish, so it would become: =IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",VLOOKUP(E2,'sh t1'!A:B,2,0)) Hope this helps. Pete On Feb 2, 3:42*pm, SteveDB1 wrote: morning all. I want to look through a range of cells and match two cells, then in a third cell place a specific value. I.e., something akin to the following. Sht1-column A * * * * sht1-ColumnB * * sht2-Column E * * *sht2-ColumnF * * 12345 * * * * * * * * * * * *A * * * * * * * * * * 34258 * * * * * * * * * ? * * * * * * * * * * * * * * * * * 34258 * * * * * * * * * * * *B * * * * * * * * * * blank * * * * * * * * * *? * * 56789 * * * * * * * * * * * *C * * * * * * * * * *12345 * * * * * * * * * *? For sht2-ColumnF I want to do a worksheet function that will match up the matching 5 digit numbers sht1-columnA and sht2-Column E, and then insert the correct letter to column F. Last week I'd tried something akin to: =if(A2=match('sht2'!F2,f1:f190,0),vlookup(......), "") and of course it failed. This morning I realized that it was because my match only returns the location if the response is true, and and N/A error if the value cannot be found. I need a Letter input in the F column IF the 5 digit number series matches, and blank if there's no 5 digit series in Column E. If this is not clear, please advise, and I'll try explaining it differently. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
Hi Roger.
Hope all has been well with you and yours. That did indeed do what I wanted. Thank you. As I've been telling others in the programming "room"-- yet another satisfied customer.... Again-- thanks. Best. "Roger Govier" wrote: Hi Steve If I have understood you correctly, then =IF(E2="","",INDEX(Sheet1!B:B,MATCH(E2,Sheet1!A:A, 0))) -- Regards Roger Govier "SteveDB1" wrote in message ... morning all. I want to look through a range of cells and match two cells, then in a third cell place a specific value. I.e., something akin to the following. Sht1-column A sht1-ColumnB sht2-Column E sht2-ColumnF 12345 A 34258 ? 34258 B blank ? 56789 C 12345 ? For sht2-ColumnF I want to do a worksheet function that will match up the matching 5 digit numbers sht1-columnA and sht2-Column E, and then insert the correct letter to column F. Last week I'd tried something akin to: =if(A2=match('sht2'!F2,f1:f190,0),vlookup(......), "") and of course it failed. This morning I realized that it was because my match only returns the location if the response is true, and and N/A error if the value cannot be found. I need a Letter input in the F column IF the 5 digit number series matches, and blank if there's no 5 digit series in Column E. If this is not clear, please advise, and I'll try explaining it differently. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
You guys are masters.....
Both work, and all three provide the same answers. These will definitely be placed in my Excel toolbox. Thank you. "Pete_UK" wrote: Try it this way in F2 of sht2: =IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",INDEX('sht1'!B :B,MATCH(E2,'sht1'! A:A,0))) then copy it down. You could replace the INDEX part with your VLOOKUP if you wish, so it would become: =IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",VLOOKUP(E2,'sh t1'!A:B,2,0)) Hope this helps. Pete On Feb 2, 3:42 pm, SteveDB1 wrote: morning all. I want to look through a range of cells and match two cells, then in a third cell place a specific value. I.e., something akin to the following. Sht1-column A sht1-ColumnB sht2-Column E sht2-ColumnF 12345 A 34258 ? 34258 B blank ? 56789 C 12345 ? For sht2-ColumnF I want to do a worksheet function that will match up the matching 5 digit numbers sht1-columnA and sht2-Column E, and then insert the correct letter to column F. Last week I'd tried something akin to: =if(A2=match('sht2'!F2,f1:f190,0),vlookup(......), "") and of course it failed. This morning I realized that it was because my match only returns the location if the response is true, and and N/A error if the value cannot be found. I need a Letter input in the F column IF the 5 digit number series matches, and blank if there's no 5 digit series in Column E. If this is not clear, please advise, and I'll try explaining it differently. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
You're welcome, Steve - thanks for feeding back.
Pete On Feb 2, 4:28*pm, SteveDB1 wrote: You guys are masters..... Both work, and all three provide the same answers. These will definitely be placed in my Excel toolbox. Thank you. "Pete_UK" wrote: Try it this way in F2 of sht2: =IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",INDEX('sht1'!B :B,MATCH(E2,'sht1'! A:A,0))) then copy it down. You could replace the INDEX part with your VLOOKUP if you wish, so it would become: =IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",VLOOKUP(E2,'sh t1'!A:B,2,0)) Hope this helps. Pete On Feb 2, 3:42 pm, SteveDB1 wrote: morning all. I want to look through a range of cells and match two cells, then in a third cell place a specific value. I.e., something akin to the following. Sht1-column A * * * * sht1-ColumnB * * sht2-Column E * * *sht2-ColumnF * * 12345 * * * * * * * * * * * *A * * * * * * * * * * 34258 * * * * * * * * * ? * * * * * * * * * * * * * * * * * 34258 * * * * * * * * * * * *B * * * * * * * * * * blank * * * * * * * * * *? * * 56789 * * * * * * * * * * * *C * * * * * * * * * *12345 * * * * * * * * * *? For sht2-ColumnF I want to do a worksheet function that will match up the matching 5 digit numbers sht1-columnA and sht2-Column E, and then insert the correct letter to column F. Last week I'd tried something akin to: =if(A2=match('sht2'!F2,f1:f190,0),vlookup(......), "") and of course it failed. This morning I realized that it was because my match only returns the location if the response is true, and and N/A error if the value cannot be found. I need a Letter input in the F column IF the 5 digit number series matches, and blank if there's no 5 digit series in Column E. If this is not clear, please advise, and I'll try explaining it differently.- Hide quoted text - - Show quoted text - |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
you're more than welcome.....
This is a challenge I've been struggling with for a while, and finally committed myself last week to trying various worksheet functions nested to find a solution. After my failed if-match-vlookup, I decided to post. It's somewhat ironic that it turns out I was closer than I realized. Thus-- I really appreciate the time you guys take on helping us figure out where we've missed it. Have a great week. "Pete_UK" wrote: You're welcome, Steve - thanks for feeding back. Pete On Feb 2, 4:28 pm, SteveDB1 wrote: You guys are masters..... Both work, and all three provide the same answers. These will definitely be placed in my Excel toolbox. Thank you. "Pete_UK" wrote: Try it this way in F2 of sht2: =IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",INDEX('sht1'!B :B,MATCH(E2,'sht1'! A:A,0))) then copy it down. You could replace the INDEX part with your VLOOKUP if you wish, so it would become: =IF(ISNA(MATCH(E2,'sht1'!A:A,0)),"",VLOOKUP(E2,'sh t1'!A:B,2,0)) Hope this helps. Pete On Feb 2, 3:42 pm, SteveDB1 wrote: morning all. I want to look through a range of cells and match two cells, then in a third cell place a specific value. I.e., something akin to the following. Sht1-column A sht1-ColumnB sht2-Column E sht2-ColumnF 12345 A 34258 ? 34258 B blank ? 56789 C 12345 ? For sht2-ColumnF I want to do a worksheet function that will match up the matching 5 digit numbers sht1-columnA and sht2-Column E, and then insert the correct letter to column F. Last week I'd tried something akin to: =if(A2=match('sht2'!F2,f1:f190,0),vlookup(......), "") and of course it failed. This morning I realized that it was because my match only returns the location if the response is true, and and N/A error if the value cannot be found. I need a Letter input in the F column IF the 5 digit number series matches, and blank if there's no 5 digit series in Column E. If this is not clear, please advise, and I'll try explaining it differently.- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
worksheet function...
Well, Steve, hopefully you'll do the same when you've built up your
own pool of knowledge. Pete On Feb 2, 5:24*pm, SteveDB1 wrote: you're more than welcome..... This is a challenge I've been struggling with for a while, and finally committed myself last week to trying various worksheet functions nested to find a solution. After my failed if-match-vlookup, I decided to post. It's somewhat ironic that it turns out I was closer than I realized. Thus-- I really appreciate the time you guys take on helping us figure out where we've missed it. Have a great week. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need VBA for a worksheet function | Excel Discussion (Misc queries) | |||
Which worksheet function would I use? | Excel Worksheet Functions | |||
WorkSheet Function Help | Excel Worksheet Functions | |||
Reference the worksheet from a multiple worksheet range function ( | Excel Worksheet Functions | |||
Can the offset worksheet function reference another worksheet | Excel Worksheet Functions |