2

enter image description here

I have a dummy data for origin and destination, I want to find list of cities as text which are 4 hrs. of destination. It should give me a list of cities when I change the origin. Can this be done easily by doing some lookup and match function?

JMP
  • 4,417
  • 17
  • 30
  • 41
Jogi Singh
  • 49
  • 9
  • 1
    From Delhi to Kolkata is 6 hours, in the other way it's only 4 (or vice versa), is that correct? – Dominique Aug 09 '21 at 11:49
  • sorry for the confusion, I read this left to right (origin- destination), like revers "L", should give the output of city names which are under 4 hrs. of drive, excluding the 0, I have updated the screen shot. – Jogi Singh Aug 09 '21 at 11:56
  • One way is to use sql queries in Excel – okzoomer Aug 09 '21 at 12:06
  • I am not sure on how sql will work, was thinking if this can be done easily by doing some lookup and match function? – Jogi Singh Aug 09 '21 at 12:18
  • Hi please kindly @others if they are not OP. I didn't get a notification. – okzoomer Aug 09 '21 at 13:10
  • [Tutorial 1](https://analystcave.com/create-microsoft-query-excel-excel-query/) Vlookup match and if statements would leave blank lines instead of having everything neatly written together – okzoomer Aug 09 '21 at 13:17

1 Answers1

3

indian cities

The value to lookup is in A8, and this is matched to the first column:

=XMATCH(A8,A2:A6)

Then return this row from the numerical data using INDEX (A10):

=INDEX(B2:F6,A9,)

The last comma is needed to return the entire row.

FILTER this to return only the values greater than B8 (the criteria) (A11):

=FILTER(A10#,A10#>=B8)

Then multi-match using XLOOKUP (A12):

=XLOOKUP(A11#,A10#,B1:F1)

Put it all together using LET to save space (A14):

=LET(A,INDEX(B2:F6,A9,),XLOOKUP(FILTER(A,A>=B8),A,B1:F1))
JMP
  • 4,417
  • 17
  • 30
  • 41
  • 1
    Many thanks, this is working as expected. Appreciate your quick help on this. Thanks to other also who commented and given input. cheers! – Jogi Singh Aug 09 '21 at 17:19