Solved

Find Data

Posted on 2011-02-21

Microsoft Excel

1 solution

Medium Priority

191 Views

Last Modified: 2012-05-11

Hello, trying to find a way to locate 700mb in column a175:a250 and then located RH (%) in column b175:200, then take the data from that matched row and paste in sheet a50. Sample attached
test11.xlsx

Comment

Question by:sandramac

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 50

Accepted Solution

by:barry houdini

barry houdini earned 2000 total points

ID: 349460712011-02-21

Hello sandramac,

I put this formula in sheet2 A4 and copied across

=LOOKUP(2,1/(Sheet1!$A1:$A100=$A2)/(Sheet1!$B1:$B100=$B2),Sheet1!A1:A100)

where A2 and B2 contain the criteria

see attached

regards, barry
26837063.xlsx

LVL 42

Expert Comment

by:dlmille

ID: 349464872011-02-21

Wouldn't the range need to be rows 175 -> 250?

=Lookup(2,1/(Sheet1$A175:$A250=$A2)/(Sheet1!$B175:$B250=$B2),Sheet1!A175:A250)

or could also be written as:

=Lookup(2,1/(Sheet1$A175:$A250="700mb")/(Sheet1!$B175:$B250="RH (%)"),Sheet1!A175:A250)

?

dave

LVL 50

Expert Comment

by:barry houdini

ID: 349465892011-02-21

Hello Dave,

Well, on the attachment the data was in the first 20 rows so I went with that, I often add extra rows just to show that the formula can cope with the blank cells, obviously the ranges can be adjusted as required as long as they are all the same size.

regards, barry

Author Closing Comment

by:sandramac

ID: 349757662011-02-24

Thank you.

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel code to find and report on links in the selected ActiveWorkbook (formulas, range names, pivotTables & charts)

Article by: Dave

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…

Microsoft Excel

Connect to SAP R/3 and call custom FM from MS Excel

Article by: ExcelGuide

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.

Creating a Cool Heat Map in Excel

Video by: AnnieCushing

This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

Microsoft Excel

Enumerating Prime Numbers in Excel / VBA

Video by: Ed

Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11). The larger templa…