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

#### JCUK89

##### New Member
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?

### 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
=sumproduct(--(a2:a14=f1),--(b2:b14=g1),--(c2:c14=h1),(d2:d14))

#### JCUK89

##### New Member
=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
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

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
What isn't working? Examples?

#### JCUK89

##### New Member

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

##### New Member
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

##### MrExcel MVP, Moderator
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)``

Replies
19
Views
366
Replies
1
Views
167
Replies
0
Views
127
Replies
3
Views
157
Replies
11
Views
119