اگر اکسل کار می‌کنید حتما با جستجو در اکسل آشنا هستید. هرگاه که بخواهید در یک شیت بدنبال یک کلمه یا جمله بگردید و یا بخواهید فقط ردیف‌هایی نمایش داده‌شود که شامل یک کلمه خاص می‌باشد از این روشهای جستجو استفاده می‌کنید. به فرض داخل یک شیت هستید، می‌توانید با زدن دکمه‌های Ctrl+F پنجره جستجو را بازکرده و کلمه یا جمله موردنظرتان را تایپ و جستجو نمایید، و یا اگر نیاز به نمایش اطلاعاتی دارید که فقط شامل کلمه یا جمله‌ای خاص است کافیست برای تک‌تک ستون‌ها، یک نام انتخاب کرده و با استفاده از تب Home و زدن دکمه Sort & Filter تمامی ستون‌ها را مجهز به فیلتر کرده و با استفاده از آن کلمه مورد نظر را جستجو نمایید. اما اگر داده‌ها در یک شیت و محل جستجو در شیت دیگر باشه چه باید کرد. فرض کنید یک شیت(برای نمونه نام آن را شیت‌داده می‌گذاریم) دارید که درون آن اطلاعات و داده‌ها به‌صورت ستونی درج شده و در شیت دیگری(نام آن را شیت‌گزارش می‌گذاریم) می‌خواهید براساس نیاز برخی از اطلاعات را از شیت‌داده‌ نمایش‌دهید. برای مثال فرض کنید در شیت‌داده نام دانش‌آموزان به همراه نمره‌های آن‌ها برای هر درس قرار‌دارد و میخواهید در شیت دیگری فقط اطلاعات دانش‌آموزی که مورد نظر است را نمایش‌داده و نموداری برای آن تهیه‌نمایید. چون تعداد دانش‌آموزان بسیار است جستجوی چشمی هم کارساز نیست. روش‌های جستجوی گفته شده در بالا در این‌جا کارساز نیست، چرا که شیت‌ها مجزا می‌باشند.

نخست یک فایل اکسل بسازید و نام آن‌را SearchableCell قراردهید و آن را ذخیره کنید. دوشیت با نام‌های Data و Report ایجادکرده و داده‌های زیر را به ترتیب از ستون و سطر نخست شیت Data قرار دهید. یعنی ستون نام و نام خانوادگی در ستون نخست و ریاضی ستون دوم و به همین ترتیب تا پایان.

حال به تب FORMULAS رفته و گزینه Name Manager را انتخاب کنید تا پنجره‌ای مشابه زیر باز شود. این پنجره این امکان را فراهم می‌کند تا برای یک محدوده از داده‌ها یک نام مرتبط کنیم تا در مواقع لزوم بخصوص در تعریف یک فرمول از آن نام بجای محدوده مورد نظر استفاده نماییم.

روی گزینه New کلیک‌کنید تا پنجره مشابه زیر باز شود

در قسمت Name  کلمه IndexName  بنویسید و در قسمت Refers to مقدار فرمول زیر را قرار دهید:

=OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B),1)

دکمه‌ OK  را زده و سپس دکمه Close را بزنید. در اینجا دستور Offset را بطور مختصر شرح می‌دهیم. دستور Offset این امکان را می‌دهد تا یک محدوده با ابعاد دلخواه و حتی متغیر ایجادکنیم. یعنی مثلا در دستور بالا اگر دانش‌آموزی به داده‌ها اضافه‌گردید بلافاصله محدوده ما بزرگتر می‌شود و یا اگر دانش‌آموزی از لیست حذف شد بلافاصله محدوده کوچک می‌شود.(محدوده داینامیک). پارامترهای دستور Offset به ترتیب عبارتند از:

• reference یا سلول مرجع(در اینجا سلولB2 که اولین نام خانوادگی در لیست می‌باشد را مرجع گرفته‌ایم)
• یا rows تعداد ردیف‌هایی که پس از سلول مرجع قرار دارند و مورد استفاده نیستند(در اینجا چون خود سلول مرجع هم مورد استفاده است مقدار فوق صفر است)
• cols یا تعداد ستون‌هایی که پس از سلول مرجع قرار دارد و مورد استفاده نیستند(در اینجا چون خود سلول مرجع هم مورد استفاده است مقدار فوق صفر است)
• height یا تعداد ردیف‌هایی که می‌خواهیم استفاده‌کنیم(در اینجا چو ارتفاع و یا همان تعداد ردیف‌ها متغیر است با استفاده از دستور COUNTA تعداد ردیف‌ها را مشخص می‌کنیم)
• width یا تعداد ستون‌هایی که می‌خواهیم استفاده‌کنیم(در اینجا چون فقط یک ستون که همان نام و نام خانوادگی است مورد نظر است لذا مقدار آن 1 است)

با نگاهی به عکس زیر پارامترهای فوق را متوجه خواهید‌شد.

=OFFSET(C7,4,3,3,2)

طبق عکس می‌خواهیم در محدوده I7:J13 مقدار سلول‌های محدوده F11:G13 قراردهیم. چون داده‌ها از سلول C7 شروع می‌شود لذا سلول مرجع را همان C7 می‌گیریم(می‌توانیم هر سلولی را مرجع بگیریم) از سلول مرجع اگر 4 ردیف به پایین و 3 ستون به سمت راست برویم به اولین سلول F11 می‌رسیم که می‌خواهیم نمایش دهیم(محدوده نخودی رنگ) از سلول F11 به طول 2 و ارتفاع 3 محدوده‌ای است که نمایش می‌دهیم.

حال ادامه بحث اصلی: حال به شیت Report رفته و مطابق زیر اطلاعات را وارد کنید:

1. A1="نام مورد جستجو را وارد کنید"
2. C1="ریاضی"
3. D1="فیزیک"
4. E1="شیمی"
5. F1="ادبیات"

حال در سلول B2 مقدار زیر را قرار دهید و فقط حواستان باشد که بجای زدن دکمه Enter باید دکمه Shift+Enter زده شود.

=IF($B$1<>"",IFERROR(INDEX(IndexName,SMALL(IF(ISNUMBER(SEARCH($B$1,IndexName)),ROW(IndexName)-MIN(ROW(IndexName))+1,""),ROWS($ZZ$1:$ZZ1))),""),"")

چرایی دکمه Shift+Enter در این است که وقتی کلمه‌ای جستجو می‌شود ممکن است بیش از یک مورد یافت شود پس ما نیاز به یک آرایه داریم با زدن دکمه‌های  ما یک آرایه ایجاد می‌کنیم و عناصر آن را نشان می‌دهیم. حال به ترتیب از درونی‌ترین دستور توضیح می‌دهیم:

1. SEARCH($B$1,IndexName)

مقدار نوشته شده در سلول $B$1 در تک‌تک سلول‌های آرایه IndexName جستجو می‌کند اگر در یک سلول مقدار فوق را پیدا کند عدد محل آن را و اگر پیدا نکند مقداری غیر عددی برمی‌گرداند. مثلا اگر مقدار جستجوی ما «پیروز» باشد و یکی از سلول‌ها مقدار «آرش پیروزنام» باشد این دستور مقدار 5 را که محل حرف «پ» در «آرش پیروزنام» است را برمی‌گرداند.(آرش یک کلمه سه حرفی است و پس از آن هم یک فاصله قرار دارد پس حرف «پ» حرف 5ام می‌شود)

2. ISNUMBER(SEARCH($B$1,IndexName))

چک میکند که آیا مقدار پیدا شده عدد است یا خیر.

3. ROW(IndexName)-MIN(ROW(IndexName))+1

مقدار شماره ردیف مقدار پیدا شده را برمی‌گرداند.(توجه کنید که ما دکمه Shift+Enter را زده‌ایم لذا یک آرایه را می‌خواهیم برگردانیم پس اکسل می‌داند باید جستجو را ردیف به ردیف انجام دهد و در هر ردیف مقدار همان ردیف را برمی‌گرداند)

4. IF(ISNUMBER(SEARCH($B$1,IndexName)),ROW(IndexName)-MIN(ROW(IndexName))+1,"")

یا شماره ردیف یا مقدار خالی برمی‌گرداند

5. SMALL(IF(ISNUMBER(SEARCH($B$1,IndexName)),ROW(IndexName)-MIN(ROW(IndexName))+1,""),ROWS($ZZ$1:$ZZ1)))

اولین عنصر آرایه را برمی‌گرداند(چرا اولین؟ زیرا به دستور ROWS($ZZ$1:$ZZ1) دقت کنید تعداد ردیف‌های آن یک است می‌توانستیم بجای آن 1 بگذاریم اما دلیل آن را خواهید فهمید)

6. SMALL(IF(ISNUMBER(SEARCH($B$1,IndexName)),ROW(IndexName)-MIN(ROW(IndexName))+1,""),ROWS($ZZ$1:$ZZ1)))

بقیه موارد هم دیگر ساده بوده و فقط جلوگیری از خطا می‌کند.

مقادیر زیر را نیز در سلول‌های مربوط به خود قرار دهید

C1=IFERROR(VLOOKUP($B2,Data!$B$1:$F$8,2,FALSE),"")
D1=IFERROR(VLOOKUP($B2,Data!$B$1:$F$8,3,FALSE),"")
E1=IFERROR(VLOOKUP($B2,Data!$B$1:$F$8,4,FALSE),"")
F1=IFERROR(VLOOKUP($B2,Data!$B$1:$F$8,5,FALSE),"")

حال اگر در سلول B1 مقدار «مهر» را تیپ کنید و دکمه Enter را بزنیدنمرات «فرامرز بزرگمهر» ظاهر می‌شود.

برای اینکه بخواهیم بیش از یک مورد اطلاعات جستجو شده نمایش داده شود چه باید کرد؟

•  جستجوی اطلاعات در اکسل (12.85 کیلو بایت)