vlookup or index for text on 2 worksheets

[simpsonehh]

Hi,
I have text data on sheet1 (UserSort) in columns A and B - random order
cannot be changed
column B does not have text data in every cell, some are blank.
sheet two (Temp-A) column A has the same data as sheet1 column A. i need to
populate sheet2 column B with the data


sheet1 sheet2
A B A B
Hodge pc-hodge ralph ? (need to populate pc-hodge here)
ralph pc-ralph lacy (should be blank or #na if not data in
sheet1 column B)
jones jones
lacy smith
smith pc-lacy hodge

I have searched and adjusted these but with no luck:
IF(A9="",,"This is
true",INDEX(sheet1!$A$1:$A$1000,MATCH(TRUE,ISNUMBER(SEARCH(A9,$B$1:$B$1000)),0)))

=INDEX(UserSort!$A$2:$A$1000,MATCH('Temp-A'!A1,$B$1:$B$1000,1))

=VLOOKUP(B4,UserSort!$B$2:$B$1000,1,TRUE)

Thanks for the help.

 

[Sheeloo]

Did not understand your problem...
Do you want to search for 'ralph' in Col A of sheet 'UserSort' and get the
value in Col B against 'ralph'?

If yes then use
=VLOOKUP(A4,UserSort!$A$2:$B$1000,2,FALSE)
in B4 and copy down...
You will get #N/A if A4 (or corresponding value when copied down) is not
found..

If not then try to explain again...

 

[JLatham]

I didn't notice this question until I answered in the other posting by the
same OP, so I'll try to add a little to what Sheeloo provided (which is spot
on correct).

To inhibit the display of #N/A in the case of no match, you can modify the
formula to 'wrap' it in an error trapping section:

=IF(ISNA(VLOOKUP(A4,UserSort!$A$2:$B$1000,2,FALSE)),"No
Match",VLOOKUP(A4,UserSort!$A$2:$B$1000,2,FALSE))
or if you don't even want to see that No Match entry, and just have an empty
looking cell then:
=IF(ISNA(VLOOKUP(A4,UserSort!$A$2:$B$1000,2,FALSE)),"",VLOOKUP(A4,UserSort!$A$2:$B$1000,2,FALSE))