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

JCUK89

New Member
Joined
Mar 26, 2018
Messages
6
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:

ABCD
FIRM ASINGLE FAMILYNo Basement/Enclosure1
FIRM ASINGLE FAMILYWith Basement2
FIRM ASINGLE FAMILYWith Enclosure3
FIRM ASINGLE FAMILYElevated on Crawlspace4
FIRM ASINGLE FAMILYNon-Elevated with Subgrade Crawlspace5
FIRM ASINGLE FAMILYManufactured (Mobile) Home6
FIRM A2-4 FAMILYNo Basement/Enclosure7
FIRM A2-4 FAMILYWith Basement8
FIRM A2-4 FAMILYWith Enclosure9
FIRM A2-4 FAMILYElevated on Crawlspace10
FIRM A2-4 FAMILYNon-Elevated with Subgrade Crawlspace11
FIRM A2-4 FAMILYManufactured (Mobile) Home12
FIRM BOTHER RESIDENTIALNo Basement/Enclosure13

<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!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
=sumproduct(--(a2:a14=f1),--(b2:b14=g1),--(c2:c14=h1),(d2:d14))
 

JCUK89

New Member
Joined
Mar 26, 2018
Messages
6
=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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
You're missing a comma...

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

JCUK89

New Member
Joined
Mar 26, 2018
Messages
6

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
What isn't working? Examples?
 

JCUK89

New Member
Joined
Mar 26, 2018
Messages
6

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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? :confused:
 

JCUK89

New Member
Joined
Mar 26, 2018
Messages
6
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? :confused:


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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,109,532
Messages
5,529,393
Members
409,870
Latest member
Well59
Top