2

Im trying to specify which column to return when using Xlookup but not sure how to go about this. Sample data below:

enter image description here

My formula =XLOOKUP(1,(C3:C6="Bob")*(D3:D6="LA"),G3:G6) gets the value of column G where name is Bob and city is LA returning 78.8 in this case. However I want to be able to dynamically specify which column is to be returned out of E,F & G similar to how Vlookup can specify a column number to return, because I wont know ahead of time which column to return. How can I specify the column number to return instead of hardcoding a range

West
  • 2,350
  • 5
  • 31
  • 67
  • How formula will determine which column to return? What is logic to specify column? – Harun24hr Nov 25 '20 at 02:04
  • 2
    If you want just specify column number instead of range then could try `=INDEX(E3:G6,MATCH("Bob" & "LA",C3:C6&D3:D6,0),3)`. – Harun24hr Nov 25 '20 at 02:09
  • "_I want to be able to dynamically specify which column is to be returned..._" What does that mean? Describe how "dynamically specify" works. – Marc Nov 25 '20 at 02:16
  • @Harun24HR The users of the spreadsheet will specify which column to return. From their data entry I'm able to tell which column they need eg column 5. But I dont know how to use this column number in the xlookup. – West Nov 25 '20 at 02:16
  • @Harun24HR That works perfectly! Seems xlookup was not suited for what I needed. Thanks – West Nov 25 '20 at 02:18
  • @West Welcome! See my answer and please accept (tick mark it) if it solve your problem. – Harun24hr Nov 25 '20 at 02:21

1 Answers1

2

There are so many ways to do that. Try below one-

=XLOOKUP(1,(C3:C6="Bob")*(D3:D6="LA"),INDEX(E3:G6,,3))

enter image description here

Another way is using Index()/Match() combination.

=INDEX(E3:G6,MATCH("Bob" & "LA",C3:C6&D3:D6,0),3)
Harun24hr
  • 30,391
  • 4
  • 21
  • 36