worksheet function...

[SteveDB1]

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.

 

[Roger Govier]

Hi Steve

If I have understood you correctly, then
=IF(E2="","",INDEX(Sheet1!B:B,MATCH(E2,Sheet1!A:A,0)))

 

[Pete_UK]

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,'sht1'!A:B,2,0))

Hope this helps.

Pete

 

[SteveDB1]

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.

 

[SteveDB1]

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]

You're welcome, Steve - thanks for feeding back.

Pete

 

[SteveDB1]

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]

Well, Steve, hopefully you'll do the same when you've built up your
own pool of knowledge.

Pete