Lookup / Index 3 or more columns, return value from same row

[JCUK89]

Hi,

New here and hoping you can help me.
I have 4 columns of data and I need to be able to search for values in three columns, and return the 4th value if a match occurs. The data is as follows:

[TABLE="width: 630"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]SINGLE FAMILY[/TD]
[TD]No Basement/Enclosure[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]SINGLE FAMILY[/TD]
[TD]With Basement[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]SINGLE FAMILY[/TD]
[TD]With Enclosure[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]SINGLE FAMILY[/TD]
[TD]Elevated on Crawlspace[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]SINGLE FAMILY[/TD]
[TD]Non-Elevated with Subgrade Crawlspace[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]SINGLE FAMILY[/TD]
[TD]Manufactured (Mobile) Home[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]2-4 FAMILY[/TD]
[TD]No Basement/Enclosure[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]2-4 FAMILY[/TD]
[TD]With Basement[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]2-4 FAMILY[/TD]
[TD]With Enclosure[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]2-4 FAMILY[/TD]
[TD]Elevated on Crawlspace[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]2-4 FAMILY[/TD]
[TD]Non-Elevated with Subgrade Crawlspace[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]FIRM A[/TD]
[TD]2-4 FAMILY[/TD]
[TD]Manufactured (Mobile) Home[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]FIRM B[/TD]
[TD]OTHER RESIDENTIAL[/TD]
[TD]No Basement/Enclosure[/TD]
[TD="align: right"]13[/TD]
[/TR]
</tbody>[/TABLE]

Let's say I have the data I want to search for in cells F1, G1, H1 I want a formula to search A:A FOR F1 data, B:B for G1 data, and H1 for C:C data, and if there is a match, return the number from D:D data.

For example, if
F1 = "FIRM A"
G1 = "2-4 FAMILY"
H1 = "Elevated on Crawlspace"
return would be 10, based on data above.

Does this makes sense?

Thank you for your help!

 

[njimack]

=sumproduct(--(a2:a14=f1),--(b2:b14=g1),--(c2:c14=h1),(d2:d14))

 

[JCUK89]

=sumproduct(--(a2:a14=f1),--(b2:b14=g1),--(c2:c14=h1),(d2:d14))

Thanks Neil, that worked perfectly. I tried to add two more columns of data, to reference 5 different criteria, and I have lost the desired result.

I'm not familiar with the sumproduct function.

Here is the formula how I editted it (the reference cells are in the P column now)

=SUMPRODUCT(--(C7:C150=P2),--(B7:B150=P3),--(D7:D150=P4),--(E7:E150=P5)--(F7:F150=P6),(G7:G150))

 

[njimack]

You're missing a comma...

=SUMPRODUCT(--(C7:C150=P2),--(B7:B150=P3),--(D7:D150=P4),--(E7:E150=P5),--(F7:F150=P6),(G7:G150))

 

[JCUK89]

You're missing a comma...

=SUMPRODUCT(--(C7:C150=P2),--(B7:B150=P3),--(D7:D150=P4),--(E7:E150=P5),--(F7:F150=P6),(G7:G150))

Thanks! That was pretty stupid.

I am 99% there with what I need to do but have a really annoying issue.

I have used the formula to look up values in the columns. The data matches what is being looked up (seemingly), and for a handful of these data it is working, and for others it is not.

Cannot work out why!

I am not sure if you're allowed to upload, so I uploaded the spreadsheet, it's really the only way to see the issue. If someone could have a look, that would be great.

https://we.tl/AV1MuLKsAb

Sheet1 tab is where the formulas are (row 8). Data validation is on for these options. The 'data' tab is all of the data being looked up.

I really appreciate the help!

 

[Peter_SSs]

What isn't working? Examples?

 

[JCUK89]

What isn't working? Examples?

Hi Peter,

Certain combinations of selections in B2-B6 do not work. I think by process of elimination, any selections in B2 and B3 do work. The following examples do not work:


[TABLE="width: 329"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PRIMARY RESIDENTIAL[/TD]
[/TR]
[TR]
[TD]FIRM ZONES A AE A1-A30 AO AH D3[/TD]
[/TR]
[TR]
[TD]2-4 FAMILY[/TD]
[/TR]
[TR]
[TD]With Enclosure[/TD]
[/TR]
[TR]
[TD]Enclosure & Above


[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 329"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PRIMARY RESIDENTIAL[/TD]
[/TR]
[TR]
[TD]FIRM ZONES A AE A1-A30 AO AH D3[/TD]
[/TR]
[TR]
[TD]SINGLE FAMILY[/TD]
[/TR]
[TR]
[TD]Elevated on Crawlspace[/TD]
[/TR]
[TR]
[TD]Basement & Above[/TD]
[/TR]
</tbody>[/TABLE]



There are plenty more, I am struggling to isolate individual selections causing problems! I really appreciate you looking!

 

[Peter_SSs]

The following examples do not work:


[TABLE="width: 329"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PRIMARY RESIDENTIAL[/TD]
[/TR]
[TR]
[TD]FIRM ZONES A AE A1-A30 AO AH D3[/TD]
[/TR]
[TR]
[TD]2-4 FAMILY[/TD]
[/TR]
[TR]
[TD]With Enclosure[/TD]
[/TR]
[TR]
[TD]Enclosure & Above


[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 329"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PRIMARY RESIDENTIAL[/TD]
[/TR]
[TR]
[TD]FIRM ZONES A AE A1-A30 AO AH D3[/TD]
[/TR]
[TR]
[TD]SINGLE FAMILY[/TD]
[/TR]
[TR]
[TD]Elevated on Crawlspace[/TD]
[/TR]
[TR]
[TD]Basement & Above[/TD]
[/TR]
</tbody>[/TABLE]

There are no rows on the 'data' tab that contain those combinations, hence they are returning zero values. To me, that is working. What result do you expect them to return when there are no matching rows?

 

[JCUK89]

There are no rows on the 'data' tab that contain those combinations, hence they are returning zero values. To me, that is working. What result do you expect them to return when there are no matching rows?

Oh dear, yes you're right. Thanks, and sorry to waste your time!

 

[Peter_SSs]

Oh dear, yes you're right. Thanks, and sorry to waste your time!

No problem. Glad it is sorted in the end.

BTW, in Sheet1 you could use SUMIFS instead of SUMPRODUCT with this formula and copy it across to the right.

=SUMIFS(data!G7:G150,data!$B7:$B150,$B2,data!$C7:$C150,$B3,data!$D7:$D150,$B4,data!$E7:$E150,$B5,data!$F7:$F150,$B6)