-1

Excel File Image

Hi Everyone, Please have a look on Cell D2. I have tried the Formula 

=VLOOKUP(B2,$B$8:$D$12,3)

But it doesn't give the Result i.e. I require 04 Values as I have written there Manually but it's giving only 01 Value. I think an Array Formula might work but don't know.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Talha Rasheed
  • 1
  • 3
  • This will help: https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/ You can return multiple values as rows/columns. – Kamil Gosciminski Sep 07 '18 at 21:53
  • VLOOKUP returns the first found result. [this](https://stackoverflow.com/questions/21288782/excel-index-match-multiple-row-results) may be of use – cybernetic.nomad Sep 07 '18 at 21:54

1 Answers1

0

VLOOKUP will not work as it will only return one answer.

You will want TEXTJOIN as an array formula

=TEXTJOIN(CHAR(10),TRUE,IF($B$8:$B$12 = B2,$D$8:$D$12,""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

TEXTJOIN was introduced with Office 365 Excel.

If you do not have Office 365 Excel and still want the values in one cell like your example you will need vba.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Scott, I know about TEXTJOIN but I need to include Vlookup or Array inside the Formula as I have to import data from other Sheets in different Workbooks – Talha Rasheed Sep 07 '18 at 21:58
  • The IF iterates and returns all that match. That is what the array portion does. You would just change the references to match what you needed. – Scott Craner Sep 07 '18 at 21:59
  • =TEXTJOIN(CHAR(104),TRUE,IF(auto!L62:L65=G808,auto!N62:N65,"")) – Talha Rasheed Sep 07 '18 at 22:10
  • A notice, @TalhaRasheed your formula differ against Scott's. You use `CHAR(104)` instead of `CHAR(10)`... – Wizhi Sep 08 '18 at 08:52