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:

A	B	C	D
FIRM A	SINGLE FAMILY	No Basement/Enclosure	1
FIRM A	SINGLE FAMILY	With Basement	2
FIRM A	SINGLE FAMILY	With Enclosure	3
FIRM A	SINGLE FAMILY	Elevated on Crawlspace	4
FIRM A	SINGLE FAMILY	Non-Elevated with Subgrade Crawlspace	5
FIRM A	SINGLE FAMILY	Manufactured (Mobile) Home	6
FIRM A	2-4 FAMILY	No Basement/Enclosure	7
FIRM A	2-4 FAMILY	With Basement	8
FIRM A	2-4 FAMILY	With Enclosure	9
FIRM A	2-4 FAMILY	Elevated on Crawlspace	10
FIRM A	2-4 FAMILY	Non-Elevated with Subgrade Crawlspace	11
FIRM A	2-4 FAMILY	Manufactured (Mobile) Home	12
FIRM B	OTHER RESIDENTIAL	No Basement/Enclosure	13

<tbody>
</tbody>

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:

PRIMARY RESIDENTIAL

FIRM ZONES A AE A1-A30 AO AH D3

2-4 FAMILY

With Enclosure

Enclosure & Above

<colgroup><col></colgroup><tbody>
</tbody>

PRIMARY RESIDENTIAL

FIRM ZONES A AE A1-A30 AO AH D3

SINGLE FAMILY

Elevated on Crawlspace

Basement & Above

<colgroup><col></colgroup><tbody>
</tbody>

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:

PRIMARY RESIDENTIAL

FIRM ZONES A AE A1-A30 AO AH D3

2-4 FAMILY

With Enclosure

Enclosure & Above

<colgroup><col></colgroup><tbody>
</tbody>

PRIMARY RESIDENTIAL

FIRM ZONES A AE A1-A30 AO AH D3

SINGLE FAMILY

Elevated on Crawlspace

Basement & Above

<colgroup><col></colgroup><tbody>
</tbody>

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)