Look up Challenge

Joneye

Well-known Member
Joined
May 28, 2010
Messages
777
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
The Goal
  1. To look up the first 3 digits of the OFFICE Phone (Column E), match them in Column H and return the value in Column G

What I tried and it failed
  1. I created this formula =INDEX($H$3:$H$5,MATCH(LEFT(E4,3),G3:G5,0)) in Cell D4 and it returns N/A
My Question
  1. What forumla do i need to learn more about that can deliver the answers in ive manually entered in Cell D3,D5 & D6



ABCDEGH
NameBilling CountryBilling CountyBilling Town/CityOffice PhoneTOWNDial Code
Shop 1USATown A218-847-4444Town A218, 580
Shop 2USA
5808128888​
Town B
269​
Shop 3USATown B269-382-4578Town C800, 631
Shop 4USATown C800-237-7777
Shop 5USA
5807987111​
Shop 6USA631-207-2222
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
you need to break down the Dial code:
Town A 218
Town A 500
Town B 269
Town C 800
Town C 631

before formula use
 
Upvote 0
you need to break down the Dial code:
Town A 218
Town A 500
Town B 269
Town C 800
Town C 631

before formula use
I actually thought of this but my formula fails still, what further can you advise.
 
Upvote 0
Try in D3:
Code:
=IFERROR(INDEX($G$3:$G$8,MATCH(TRUE,INDEX(LEN($H$3:$H$8)+1-LEN(SUBSTITUTE(" "&$H$3:$H$8," "&LEFT(E3,3),""))=4,),0)),"Not found")
 
Upvote 0
Try this in D3

=IF(ISNUMBER(SEARCH(LEFT(E3,3),H3)),G3,"Not Found")
 
Upvote 0
Solution
My suggestion ..
I have assumed that column H values are text so that, for example, 269 as shown in the sample data is a text 269 not a numerical 269.

22 09 08.xlsm
ABCDEFGH
1
2NameBilling CountryBilling CountyBilling Town/CityOffice PhoneTOWNDial Code
3Shop 1USATown A218-847-4444Town A218, 580
4Shop 2USATown A5808128888Town B269
5Shop 3USATown B269-382-4578Town C800, 631
6Shop 4USATown C800-237-7777
7Shop 5USATown A5807987111
8Shop 6USATown C631-207-2222
Lookup
Cell Formulas
RangeFormula
D3:D8D3=XLOOKUP("*"&LEFT(E3,3)&"*",H$3:H$5,G$3:G$5,"Not found",2)
 
Upvote 0
Thank you this is super and simple, huge thanks
 
Upvote 0
Thank you this is super and simple, huge thanks
:confused: I don't understand. The post that you have marked as the solution does not ..
.. deliver the answers in ive manually entered in Cell D3,D5 & D6

22 09 08.xlsm
DEFGH
1
2Billing Town/CityOffice PhoneTOWNDial Code
3Town A218-847-4444Town A218, 580
45808128888Town B269
5Not Found269-382-4578Town C800, 631
6Not Found800-237-7777
75807987111
8631-207-2222
Lookup (2)
Cell Formulas
RangeFormula
D3,D5:D6D3=IF(ISNUMBER(SEARCH(LEFT(E3,3),H3)),G3,"Not Found")
 
Upvote 0
I agree with Peter.
Also, what if partial text match like this:
169-XXX in column E
and
2169 in column H
solution #5 give it a match.
???
 
Upvote 0
Peter and Bebo are correct-- I read it as one dataset (didnt notice Cols G:H was a seperate table) and was simply looking across rows- my solution is not correct!
I would go with the Xlookup solution Peter Submitted.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

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