포함되는 값만 출력 하고싶은데 vlookup으로는 힘드네요ㅠ

함수/공식
작성자
issac****
작성일
2022-08-01 09:40
조회
54
엑셀버전 : 엑셀2016

운영체제 : 윈도우10

질문 요약 : 딱 포함 되어있으면 이거 관련된 것만 출력하고 싶은데 serch랑 vlookup 으로는 안되는데 혹시 다른 방법이 있을까요?

안녕하세요 엑셀을 정식으로 사용하게 된지 이제 1개월쯤 된 엑셀 뉴비 입니다.

제가 업무하면서 업무특성상 ip들관련 정리할 때가 많은데요 예시를 들어드리면

https://1.1.1.1:8080 1.1.1.1
https://1.1.1.1:443 2.2.2.2
http://1.1.1.1:80 3.3.3.3
https://2.2.2.2:443 4.4.4.4

이런 느낌으로 쭉 있습니다.

보시다시피 1.1.1.1 같은 경우 다양하게 존재 하잖아요? 이게 각각 ip들 8080,443,80 만 있는 게 아니라서 처음에는 순서대로 정렬한 다음에 손으로 일일이 하다가 if, vlookup 이나 serch 같은 걸 사용해보려고 했는데 error 뜨고 포함 안되고 그런것들이 있더라구요

그래서 이런느낌으로 만들고 싶은데

https://1.1.1.1:8080 1.1.1.1 https://1.1.1.1:8080 https://1.1.1.1:443 http://1.1.1.1:80
https://1.1.1.1:443 2.2.2.2 https://2.2.2.2:443
http://1.1.1.1:80 3.3.3.3
https://2.2.2.2:443 4.4.4.4

 

고수님들께 조언 구해봅니다. 도와주세요~ㅠㅠ

회원등급 : 2레벨
포인트 : 52 EP
총질문 : 2 개 (마감율 : 50%)
채택답변 : 0 개
전체 7

  • 2022-08-01 12:53
    채택된 답변

    @issac**** 님 아래 링크에서 두번째 공식 참고해보시고..

    https://www.oppadu.com/vlookup-함수-여러개-값-출력/

    부부일치 검색으로 하려면 아래와 같이 수정하면 됩니다

    = INDEX($출력범위,SMALL(IF(ISNUMBER(SEARCH("1.1.1.1",IP범위)),ROW($찾을범위)-MIN(ROW($찾을범위))+1, ""),COLUMN(A1)))


    • 2022-08-01 14:21

      @더블유에이 님 안녕하세요 ! 답글 감사합니다. 주신 답변 참고하여 제가 직접 해보았습니다. 그런데 #REF!오류가 나더라구요 혹시 제가 잘못 입력한 부분이 있을까요? 밑의 수식은 위의 표를 기반으로 만들었습니다.

      = INDEX($D$2:$I$2,SMALL(IF(ISNUMBER(SEARCH("1.1.1.1",B2:B5)),ROW($B$2:$B$5)-MIN(ROW($B$2:$B$5))+1, ""),COLUMN(#REF!)))

       


      • 2022-08-01 14:33

        @issac**** 님 수식을 보면 COLUMN(#REF) 오류가 있습니다

        한번 확인해보세요


        • 2022-08-01 14:57

          @더블유에이 님 아 저걸 A1으로 고치니 이번에는 0으로 나오네요...ㅠㅠ


          • 2022-08-01 15:00

            거기다가 수식을 계산할 수 업습니다. 열려있는 통합문서에 순환 참조가 있지만 현재 순환을 일으키는 참조를 화면에 표시할 수 없습니다. 마지막에 입력한 수식을 편집하거나 실행취소 명령을 사용하여 입력한 수식을 제거 하십시오

            라는 안내창도 나오네요


            • 2022-08-01 15:08

              @issac**** 님 우선 CSE 배열수식으로 다시 입력해보시고

              수식 범위가 잘못 작성되어 있는 듯 합니다. $D$2:$I$2 이렇게하면 D2:I2 범위만 선택된 것이 아닌가요?

              예제를 다시 한번 살펴보고 잘 적용해보시면 해결 될 듯 합니다 ^^


              • 2022-08-01 15:40

                @더블유에이

                https://1.1.1.1:8080 1.1.1.1 https://1.1.1.1:8080 https://1.1.1.1:443 http://1.1.1.1:80 #NUM! #NUM!
                https://1.1.1.1:443 2.2.2.2 https://2.2.2.2:443
                http://1.1.1.1:80 3.3.3.3
                https://2.2.2.2:443 4.4.4.4

                오오오 프로님 덕분에 범위 설정에 오류가 있던걸 확인했습니다 .

                답변 친절하게 해주셔서 너무너무 감사합니다!

                드디어 한발 앞으로 전진하게 되었네요 프로님 덕분에 성공적으로 할 수 있었습니다.

                너무너무 감사드립니다~

                 


전체 7,301
번호 카테고리 제목 작성자 작성일 추천 조회
알림
[🏆 VBA 4주 완성 특별 스터디] 1기 - 우수 졸업생 및 후기, 축하합니다! (5)
오빠두엑셀 | 2022.07.31 | 추천 7 | 조회 842
오빠두엑셀 2022.07.31 7 842
공지사항 함수/공식
New [신규 기능 업데이트!] 👉 이제 게시글 작성시 스크린샷 복/붙이 가능합니다! 😎 (2)
오빠두엑셀 | 2022.08.04 | 추천 3 | 조회 64
오빠두엑셀 2022.08.04 3 64
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (87)
오빠두엑셀 | 2021.10.28 | 추천 116 | 조회 5953
오빠두엑셀 2021.10.28 116 5953
40987 VBA
New 선택한 셀 근처에 차트 나타내기 엑셀파일첨부파일 (1)
eexcell | 2022.08.08 | 추천 0 | 조회 23
eexcell 2022.08.08 - 23
40982 VBA
New 입력 수량에 따른 버튼 또는 레이블 생성 첨부파일
늙은베짱이 | 2022.08.08 | 추천 -2 | 조회 22
늙은베짱이 2022.08.08 -2 22
40974 함수/공식
New 사과를 치면 옆 셀에 과일로, 배추를 치면 옆 셀에 야채로 넣는 방법 첨부파일 (6) 답변완료
곡식창고 | 2022.08.08 | 추천 0 | 조회 37
곡식창고 2022.08.08 - 37
40971 기능/도구
New 이름 정의 범위 선택시 에러 발생 엑셀파일첨부파일 (3)
라운드티 | 2022.08.08 | 추천 0 | 조회 36
라운드티 2022.08.08 - 36
40966 함수/공식
New TEXTJOIN함수에서 대싱 셀값이 0일때 JOIN안되게 하는 방법 (8) 답변완료
마운틴 | 2022.08.08 | 추천 0 | 조회 41
마운틴 2022.08.08 - 41
40963 함수/공식
New 특정문자가 포함된 여러 셀값을 특정문자 별로 정리하고 싶습니다. 엑셀파일 (1)
rin**** | 2022.08.08 | 추천 0 | 조회 33
rin**** 2022.08.08 - 33
40961 기능/도구
New G메일로 받은 내용 중 회신시간이 늦은 메일들을 엑셀로 관리할 수 있는 방법이 있을까요 ? (1)
최사무엘 | 2022.08.08 | 추천 0 | 조회 28
최사무엘 2022.08.08 - 28
40960 기능/도구
New 보안경고 안뜨게 설정하려면 어떻게 옵션을 설정해야 하나요? 첨부파일 (1)
니가가라 하와이 | 2022.08.08 | 추천 0 | 조회 19
니가가라 하와이 2022.08.08 - 19
40958 차트/그래프
New 그래프 가로 축 레이블 설정 부분 첨부파일 (1)
성호 | 2022.08.08 | 추천 0 | 조회 29
성호 2022.08.08 - 29
40955 함수/공식
New 선택 셀의 좌측 셀값을 가져오는 수식이 필요합니다. 엑셀파일 (3)
낮은자 | 2022.08.08 | 추천 0 | 조회 44
낮은자 2022.08.08 - 44
40953 함수/공식
New 특정 조건에 맞추어 월 별 주차 계산을 하고 싶습니다 엑셀파일첨부파일 (2)
행주 | 2022.08.07 | 추천 0 | 조회 34
행주 2022.08.07 - 34
40952 문서서식
New 데이터 유효성 검사 - 드롭다운 표시 항상 보이게? 첨부파일 (1)
두리둥 | 2022.08.07 | 추천 0 | 조회 30
두리둥 2022.08.07 - 30
40945 VBA
New 엑셀 파일 엑셀 다른이름 자동 저장이 가능할까요? 노가다 살려주세요 ㅠㅠ (2)
듀링e | 2022.08.07 | 추천 0 | 조회 56
듀링e 2022.08.07 - 56
40937 파워쿼리/피벗
New 시트별로 열이 다른 경우 시트를 통합하는 방법 첨부파일 (1)
곰도리 | 2022.08.07 | 추천 0 | 조회 37
곰도리 2022.08.07 - 37
40936 VBA
New 달력 유저폼 실행위치관련 질문 엑셀파일첨부파일 (6) 답변완료
033cola | 2022.08.07 | 추천 1 | 조회 46
033cola 2022.08.07 1 46
40921 함수/공식
New 값이 있는 경우에만 DATE 함수가 적용되도록 하고 싶습니다. 첨부파일 (2) 답변완료
지용 | 2022.08.06 | 추천 0 | 조회 47
지용 2022.08.06 - 47
40916 문서서식
New 이동옵션에서 상수와 수식 첨부파일 (1)
황혼고래 | 2022.08.06 | 추천 0 | 조회 50
황혼고래 2022.08.06 - 50
40915 문서서식
New 셀 서식 중 "월", 월 차이 (2)
보라색양털 | 2022.08.06 | 추천 1 | 조회 47
보라색양털 2022.08.06 1 47
40912 함수/공식
New IF 조건문이 잘 되지 않습니다. 첨부파일 (5) 답변완료
비트리 | 2022.08.06 | 추천 0 | 조회 44
비트리 2022.08.06 - 44
40908 VBA
New 매월 초 100개의 파일을 100개의 거래처로 메일 보내는 방법 문의 (1)
alth**** | 2022.08.06 | 추천 0 | 조회 55
alth**** 2022.08.06 - 55
40906 기능/도구
New 시트 이동복사 했는데 똑같이 보여지지 않아요 (1) 답변완료
pooh09**** | 2022.08.06 | 추천 1 | 조회 45
pooh09**** 2022.08.06 1 45
40904 문서서식
New 4명의 일일실적을 월별 총합으로 보기좋게 작성할 수 있을까요? (4) 답변완료
nobody | 2022.08.06 | 추천 0 | 조회 46
nobody 2022.08.06 - 46
40903 함수/공식
New =YahooFinanceHistory( )사용방법 문의 (2) 답변완료
eexcell | 2022.08.06 | 추천 0 | 조회 36
eexcell 2022.08.06 - 36
40902 VBA
New Page_Setup에서 codename 전달 (2) 답변완료
도다리 | 2022.08.05 | 추천 0 | 조회 33
도다리 2022.08.05 - 33
40898 함수/공식
New 함수 해석좀... (2)
sky3**** | 2022.08.05 | 추천 0 | 조회 45
sky3**** 2022.08.05 - 45
40896 함수/공식
New 고급11강- 필터 함수 공부중 입니다 엑셀파일 (2) 답변완료
세레나 | 2022.08.05 | 추천 0 | 조회 39
세레나 2022.08.05 - 39
40895 VBA
New Get_DB 전체 행이 불러와지지 않습니다 엑셀파일첨부파일 (1)
claire | 2022.08.05 | 추천 0 | 조회 37
claire 2022.08.05 - 37
40892 기능/도구
New 엑셀 셀에 달력 넣기 첨부파일 (1) 답변완료
포에버하윤둥이 | 2022.08.05 | 추천 0 | 조회 41
포에버하윤둥이 2022.08.05 - 41
40889 함수/공식
New 엑셀 여러열 일치하는 값 대입 엑셀파일첨부파일 (5)
| 2022.08.05 | 추천 0 | 조회 46
2022.08.05 - 46
40887 VBA
New vba combobox에 목록이 처음 들어 갔을 때 안 떠요 엑셀파일첨부파일 (2) 답변완료
ehfkdpah**** | 2022.08.05 | 추천 0 | 조회 27
ehfkdpah**** 2022.08.05 - 27