Need help to find a specific text in a range then return the cell value

lilcarp87

New Member
Joined
Apr 6, 2021
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
I usually hit Ctrl+F, type the item and scroll up to see which box as title/headline contains the item I am looking for.
Can anyone give me a function at G3 to make it automatically shows up which box (for example: Box 1 or Box 2 or Box 3) when I use a hand scanner (the type that UPS checker uses) or sometime I have type in the exact text to input at G2.

Thanks a lot, your function will save me time. lots of time.
ask-excel.png
 

Attachments

  • ask excel.png
    ask excel.png
    17.3 KB · Views: 2

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Perhaps
Excel Formula:
 =INDEX(A1:C1,SUMPRODUCT(--(A2:C4=G2)*COLUMN(A1:C4)))
 
Upvote 0
Perhaps
Excel Formula:
 =INDEX(A1:C1,SUMPRODUCT(--(A2:C4=G2)*COLUMN(A1:C4)))
Thank you for your quick response but it shows Error instead of giving me the Box 1 when I copy and paste SKU56789 in G2 cell.
ask-excel.png
 
Upvote 0
I have found a solution, it is:
Excel Formula:
=INDEX($A$1:$C$1,SUMPRODUCT(($A$2:$C$6=G2)*COLUMN($A$1:$C$1)))

Btw, thanks for helping me out! I appreciate your fast reply.
 
Upvote 0
Solution
Adapt your ranges as needed of course
Excel Formula:
=INDEX(A1:C1,SUMPRODUCT(--(A2:C6=G2)*COLUMN(A1:C6)))
 
Last edited:
Upvote 0
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need help to find a specific text in multiple columns then return the cell [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top