Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or index for text on 2 worksheets
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,ISNUMBE R(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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or index for text on 2 worksheets
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... "simpsonehh" wrote: 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,ISNUMBE R(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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup or index for text on 2 worksheets
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)) "Sheeloo" wrote: 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... "simpsonehh" wrote: 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,ISNUMBE R(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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP MATCH INDEX two conditions / criterias text and date | Excel Worksheet Functions | |||
Index/Match Formula across 2 worksheets | Excel Worksheet Functions | |||
VLOOKUP or maybe INDEX | Excel Worksheet Functions | |||
Should I use VLOOKUP? IF? INDEX? | Excel Worksheet Functions | |||
INDEX(LARGE( across worksheets | Excel Worksheet Functions |