Trying to match 1 value to multiple columns
in [Functions]
|
From: Eduardo on 25 Mar 2010 14:38 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" wrote: > 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" wrote: > > > 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)) > > > > "Eduardo" wrote: > > > > > 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 > > > > > > "sjoseph371" wrote: > > > > > > > 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.
From: sjoseph371 on 25 Mar 2010 16:03 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. "Eduardo" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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)) > > > > > > "Eduardo" wrote: > > > > > > > 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 > > > > > > > > "sjoseph371" wrote: > > > > > > > > > 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.
From: Herbert Seidenberg on 25 Mar 2010 18:43 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
From: sjoseph371 on 26 Mar 2010 08:28 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 "Herbert Seidenberg" wrote: > 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 > > . >
From: sjoseph371 on 26 Mar 2010 09:55 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! "sjoseph371" wrote: > 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. > > "Eduardo" wrote: > > > 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" wrote: > > > > > 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" wrote: > > > > > > > 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)) > > > > > > > > "Eduardo" wrote: > > > > > > > > > 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 > > > > > > > > > > "sjoseph371" wrote: > > > > > > > > > > > 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.
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Repost - Functions on filtered data Next: convert vertical to horizontal |