extract of data with 3 different conditions?

  • Thread starter Thread starter P2000
  • Start date Start date
P

P2000

I have data:
Worksheet 1
Name dept group host
A 1 A K
A 1 B F
A 2 A G
C 1 B D
D 2 C F

Worksheet 2
Name dept group host
A 1 A


Is there a auto lookup formula where I can pull data from worksheet 1 column
"host" to another worksheet with the Name, dept, group conditions met.
Eg Name =A, dept=1, and group=A. It will lookup from the table and return
host k.
 
One way
In Sheet2,
In D2, normal ENTER to confirm
=INDEX(Sheet1!D$2:D$6,MATCH(1,INDEX((Sheet1!A$2:A$6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0))
Copy down. Adjust the ranges to suit the actual extents of your source data.
Modify easily to either remove or to add-on criteria bits as needed.
Success? Hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Thanks it work perfectly, but can we return a "Nil" message in the host
column if the index is unable to satisify any one of the name, dept, group.
Currently I got the error #N/A if any of the group is not met.
 
Indicatively, use an IF(ISNA error trap on the MATCH bit, viz.:
=IF(ISNA(MATCH(..)),"Nil",INDEX(..))

In D2, copied down
=IF(ISNA(MATCH(1,INDEX((Sheet1!A$2:A$6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)),"Nil",INDEX(Sheet1!D$2:D$6,MATCH(1,INDEX((Sheet1!A$2:A$6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)))
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
 
Got it! Thank you.

Max said:
Indicatively, use an IF(ISNA error trap on the MATCH bit, viz.:
=IF(ISNA(MATCH(..)),"Nil",INDEX(..))

In D2, copied down:
=IF(ISNA(MATCH(1,INDEX((Sheet1!A$2:A$6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)),"Nil",INDEX(Sheet1!D$2:D$6,MATCH(1,INDEX((Sheet1!A$2:A$6=A2)*(Sheet1!B$2:B$6=B2)*(Sheet1!C$2:C$6=C2),),0)))
voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
Click to expand...
 
Hello,

Max, I actually saw your posting out of an internet search and it helped me out a whole lot. Thank you.

I have a few problems. I'm using your formula to read data from a table that will be copied and pasted onto the sheet. When I copy and paste the new table from which the data will be extracted, the formulas don't update automatically. I would have to go to each cell where I used the formula and click on the equation bar and press enter before it calculates. Is there a way I can make the already input formulas run automatically as soon as I copy and paste the data source table?

The formulas work but There is a very large data set and it is a pain go to each cell and push enter each time to make the formulas work.
 
Max,
I found your posting randomly and it helped me out A LOT. Thank you,

However I had a problem when I ran the formula. The table from which the data will be extracted will be copied and pasted on the sheet. When I paste the table on the sheet, the formulas don't automatically calculate to extract the values. I would have to click each cell, click the equation bar at the top, then press enter before the the formulas calculate. Is there a way I can get the formulas run automatically as soon as I paste the table on the sheet?

I have a large data set so it's a pain to go through each and click, click the bar and push enter.

Thank you
 
Max,
I found your posting randomly and it helped me out A LOT. Thank you,

However I had a problem when I ran the formula. The table from which the data will be extracted will be copied and pasted on the sheet. When I paste the table on the sheet, the formulas don't automatically calculate to extract the values. I would have to click each cell, click the equation bar at the top, then press enter before the formulas calculate. Is there a way I can get the formulas run automatically as soon as I paste the table on the sheet?

I have a large data set so it's a pain to go through each and click, click the bar and push enter.

Thank you
 
Max,
I found your posting randomly and it helped me out A LOT. Thank you,

However I had a problem when I ran the formula. The table from which the data will be extracted will be copied and pasted on the sheet. When I paste the table on the sheet, the formulas don't automatically calculate to extract the values. I would have to click each cell, click the equation bar at the top, then press enter before the the formulas calculate. Is there a way I can get the formulas run automatically as soon as I paste the table on the sheet?

I have a large data set so it's a pain to go through each and click, click the bar and push enter.

Thank you
 
Sounds like your book is "inadvertently" set to manual calculation
mode
If you press F9, does it all calculate properly?

To change the setting (steps in Excel 2003, my ver)
Click Tools > Options > Calculation tab
Ensure "Automatic" is checked > OK
 
Back
Top