Lookup formula

[Newbienew]

The issue that I am having is, is that I have names in one column and the team they belong to in another. I would like to auto populate the team the name belongs to. However, the way the names and teams are broken down I don't know how to make it work out. I have provided an example that would give a visual idea as well as an explanation

https://www.dropbox.com/s/6va9bdalqus0xfw/Team Search Listing.xlsx?dl=0

 

[James006]

Re: Help with lookup formula

Hello,

Thanks for the DropBox test file ...

In sheet1 cell A2, you can have the following Array formula:

=INDEX(Sheet2!$1:$1,MIN(IF(Sheet2!$A$1:$D$8=B2,COLUMN($A$1:$D$8))))

Instead of the standard Enter key ... you need to use simultaneaously the three keys : Control Shift Enter

Hope this will help

 

[Newbienew]

Re: Help with lookup formula

I attempted to use the formula but my example is on a smaller scale. I was looking to expand where the the names come from, but it did not work out for me. It did work for me but for some reason only for a short array? I also had an issue with typing the formula in v.s. copy and pasted. {} show up on either side of the entire formula. Never seen that before


=INDEX(Sheet2!$1:$1,MIN(IF(Sheet2!$A$1:$D$12=B2,COLUMN($A$1:$D$12))))

 

[James006]

Re: Help with lookup formula

Thanks for your Thanks ...

Is the Array formula producing your expected results ... or not ..???

 

[Newbienew]

Re: Help with lookup formula

It works on the example when i cut and paste. When I type it out on another work book it will only give me the result of TEAM A. But my example mirrors the actual spreed sheet that I am working with. The only difference is, is that column be have an IFERROR Vlookup formula in it. I am not sure if his has something to do with it or not.

https://www.dropbox.com/s/6va9bdalqus0xfw/Team Search Listing.xlsx?dl=0

 

[James006]

Re: Help with lookup formula

Hello,

The only thing you did not pay attention to ...

Once you type the Array Formula :

Instead of the standard Enter key ... you need to use simultaneously the three keys : Control Shift Enter

=INDEX(Sheet2!$1:$1,MIN(IF(Sheet2!$A$1:$D$13=B4,COLUMN($A$1:$D$13))))

Then ...

 

[Newbienew]

Re: Help with lookup formula

I figured it out. thank you gain for the repost. I do have a question. How would i go about if the cell is blank in b2 that the TEAM A does not appear. I didn't realize it until a name was not present in the B column. Is there a way of solving is? Thank you

 

[James006]

Re: Help with lookup formula

Glad you could fix it ...

Are you after adjusting your formula for a potential error ...?

 

[Newbienew]

Re: Help with lookup formula

What is happening is, if there is nothing in cell b2 or any cell in the column a2 or any cell in the column auto-defaults to Team A. I was looking for it to be blank just as b2. If this is possible.

 

[James006]

Re: Help with lookup formula

Have a go with following

=Iferror([COLOR=#333333]INDEX(Sheet2!$1:$1,MIN(IF(Sheet2!$A$1:$D$13=B4,COLUMN($A$1:$D$13)))),"")[/COLOR]

Should avoid any errors ...