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.

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.

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.

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.

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.

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 -

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 -

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.