vlookup with IF statements?

[jeptik]

Hi All,

I have a file with some data that I need to summarise based on the location.

I need to bring in the data for Type and Total into the Formula worksheet whenever I filter for Location. (see attached)

I am thinking it needs a vlookup with a couple of IF statements or and IFAND statements? not sure.

Any help is appreciated.

Thanks!

[63falcondude]

Since there can be multiple matches, I'll assume that you want to list those matches going down columns C and D.

Try this:

C3 =IFERROR(INDEX(Data!B:B,SMALL(IF(Data!$A$2:$A$37=$B$3,ROW(Data!$A$2:$A$37)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula to D3 then down as far as needed.

[jeptik]

Hi 63falcondude,

Sorry, I think I messed up on my request and I apologise.

What I am trying to get do is to have the formula populate the types with the total based on me changing the location. (see attached updated file).

Once again I apologise for my previous mistake.

Thanks!

[FDibbins]

Since there can be multiple matches, I'll assume that you want to list those matches going down columns C and D.

Try this:

C3 =IFERROR(INDEX(Data!B:B,SMALL(IF(Data!$A$2:$A$37=$B$3,ROW(Data!$A$2:$A$37)),ROWS($1:1))),"") Ctrl Shift Enter

Drag the formula to D3 then down as far as needed.

That formula needs to be entered using CTRL SHIFT ENTER, not just enter. Looks like 63 forgot to mention that (I know I have, a few times already lol)

[jeptik]

Or is it an Index Match statement?

[63falcondude]

If I am understanding you correctly, that's what the formula in post #2 does.

For example, when B3 is 10010, the Types would be 1 Bed and 2 Bed and the Totals would be 4.5 and 3.1.

When B3 is changed to let's say 10021, the Types would be 1 Bed, 2 Bed, and BACHELOR and the Totals would be 3.8, 2.5, and 4.0.

Is this not what you want?

[jeptik]

Hi 63falcondude,

Forgive my ignorance, but is it possible for you to do the formula in the example file I attached so that I can see how it works for the locations?

I tried it but couldn't get it to work the way it should.

I am new to these extensive formulas, hence the inability to make it work.

Thanks!

[63falcondude]

Sure. Attached is the sample from post #1 with the formula from post #2. Note that I edited the drop-down to include everything.

I dragged the formula down through row 10.

[63falcondude]

Looks like 63 forgot to mention that

It's shown in the bold part of post #2.

[FDibbins]

oops missed that, apologies

[63falcondude]

I think the O.P. may have missed it as well. No worries! Let's see if that was the culprit.

[jeptik]

Hi 63falcondude,

Thanks for the example.

Now I get it and it works! I did miss the Ctrl Shift Enter. Thank you both for your help.

Cheers!

[FDibbins]

As long as you got where you needed to be. That CSE thing is easy to overlook or forget
Thanks for the feedback

[63falcondude]

Glad we could help. Thanks for the rep!