Trying to match 1 value to multiple columns

[sjoseph371]

I have a spreadsheet with several different worksheets. On Worksheet 1, I
have the following:
(Col A) Structure Number, (Col B) Opening Height 1, (Col C) Opening
Direction 1 (N, S, E, W), (Col D) Opening Height 2, (Col E) Opening Direction
2 (N, S, E, W), (Col F) Opening Height 3, (Col G) Opening Direction 2 (N, S,
E, W), (Col H) Opening Height 4, (Col I) Opening Direction 4 (N, S, E, W)
There are over 100 rows, 1 for each structure. Some structures have just 1
opening, and some have multiple openings.

On Worksheet 2, the user will enter:
(Col A) Structure Number and (Col B) Opening Direction. I want Column C to
search Worksheet 1 and give the Opening Height automatically.

For example, Worksheet 1 has:

(A) (B) (C ) (D) (E) (F) (G)
(row) STR # OPENING1 OPENING1 OPENING2 OPENING2 OPENING3 OPENING3
HGT DIR HGT DIR HGT DIR
1 95-01 2.400 NE 2.300 SE
2 95-02 3.050 SW
3 95-03 4.900 E 4.900 W
4 95-04 4.880 SW 4.880 E 5.730
NE

On Worksheet 2, when the user enters 95-01 in Col A and SE in Col B, I want
the program to automatically give me 2.300 in Col C. Is there a function (or
combination of functions) that lets me do this.

 

[Eduardo]

Hi,

=index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000))

change range to meet your needs

 

[Eduardo]

opps I missed something use

=index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000,0))

 

[sjoseph371]

opps I missed something use

=index(sheet1!$D$1:$D$1000,match(a2,sheet1!$A$1:$A$1000,0),match(B2,sheet1!$C$1:$C$1000,0))

 

[sjoseph371]

Thanks for the speedy reply, however, 2 things:
1. I received a REF! error
2. The formula you suggested would not work if the user entered in Str
91-01 Opening Dir NE on Sheet 2. Is there a formula that would check the
whole spreadsheet?
Thanks

 

[Eduardo]

Hi,
91-01 has to be in sheet 1, what the formula does is

index = is the field where the information is taking from
first match look for 95-01 or what you enter in a2 in sheet 1 then goes to
the secongd match and look for the opening direction when both criterias are
met it pull the information from column C, in sheet 1 both the opening
direction and the structure number have to exist

 

[sjoseph371]

I got it to work - kind of . . .
First, I had to do the {} thing around the formula.

But, what if the information is NOT in column C.
i.e. when I entered in
Str = 95-03
Opening Dir = W
The result was "N/A" since W was in Column E.

Is there a way to get it to search Column E if it does not find the value in
Column C, then if it's not in Column C or E to search in Column G?

Thanks again.

 

[Herbert Seidenberg]

Excel 2007 Table
And/Or search
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_24_10c.xlsx
Pdf preview:
http://c0718892.cdn.cloudfiles.rackspacecloud.com/03_24_10c.pdf

 

[sjoseph371]

Herbert,
Thanks for the info and all of teh work it looks like you put into your
solution, but I should have clarified that I have Excel 2003 and it doesn't
support the function you suggested. Unfortunately, I'm using this at my
workplace, and 2003 is the only version they have and probably won't be
updated anytime. We're a large company, so there's the licensing issue, but
not so large that it's not a financial burden to do so.
If you have a suggestion for the 2003 version, I'd appreciate it.
Again, thanks for all of your work.
Joe

 

[sjoseph371]

Eduardo & Herbert,
Thanks for your help so far. I think I came up with a formula that might
work, if EXCEL 2003 would allow more than 7 functions. It's a long IF THEN
that would do the following:
If it checks Opening1 Height & Opening1 Dir and does not find the
corresponding Dir, it would see that the result would be "N/A", tehn go to
the Opening 2 fields, and so on, until it finds the right Opening Dir.
However, the formula is pretty long, involved, and too much for Excel 2003 to
handle:

=IF(ISNA(INDEX(Sheet1!$B$1:$B$4,MATCH(1,(Sheet1!$A$1:$A$4=Sheet2!A3)*(Sheet1!$C$1:$C$4=Sheet2!B3),0)))=false,(INDEX(Sheet1!$B$1:$B$4,MATCH(1,(Sheet1!$A$1:$A$4=Sheet2!A3)*(Sheet1!$C$1:$C$4=Sheet2!B3),0))),IF(ISNA(INDEX(Sheet1!$D$1:$D$4,MATCH(1,(Sheet1!$A$1:$A$4=Sheet2!A3)*(Sheet1!$E$1:$E$4=Sheet2!B3),0)))=false,(INDEX(Sheet1!$D$1:$D$4,MATCH(1,(Sheet1!$A$1:$A$4=Sheet2!A3)*(Sheet1!$E$1:$E$4=Sheet2!B3),0)))),IF(ISNA(INDEX(Sheet1!$f$1:$f$4,MATCH(1,(Sheet1!$A$1:$A$4=Sheet2!A3)*(Sheet1!$g$1:$g$4=Sheet2!B3),0)))=false,(INDEX(Sheet1!$f$1:$f$4,MATCH(1,(Sheet1!$A$1:$A$4=Sheet2!A3)*(Sheet1!$g$1:$g$4=Sheet2!B3),0)))))

Is there a way to condense this down into an easy to read function for Excel
2003. Would a Macro be able to handle it? If a macro is required & able to
handle it, can you walk me through it since I have NO experience in Macros?

Thanks for help on this tough nut!

 

[Herbert Seidenberg]

Excel 2003
Converted from previous 2007 file.
http://www.mediafire.com/file/ddouwzfzton/03_24_10c.xls