how can I lookup with two values in Excel

how can I lookup with two values in Excel. Please read sheet 2 from sheet 1 in the attached example.
grid-map-analysis.xlsx
Mohammed DallagPetroleum ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try as an array formula (CtrlShift+Enter)
=INDEX(Sheet1!C2:C3000,MIN(IF(--(Sheet1!A2:A3000>=Sheet2!E11)*--(Sheet1!B2:B3000>=Sheet2!F11)*--(ROW((Sheet1!A2:A3000))),--(Sheet1!A2:A3000>=Sheet2!E11)*--(Sheet1!B2:B3000>=Sheet2!F11)*--(ROW((Sheet1!A2:A3000))),"")))

Open in new window

Regards
grid-map-analysisV1.xlsx
0
SteveCommented:
There is not a strict 'Lookup' in the file as none of your values for X or Y match in the table.

A close aproximation may be found using the following:
=INDEX(Sheet1!$C$2:$C$2501,MATCH(Sheet2!F11&"|"&Sheet2!E11,Sheet1!$B$2:$B$2501&"|"&Sheet1!$A$2:$A$2501,1))

Open in new window

Press [ctrl]+[shift]+[enter] when putting it in cell G11 and drag down.

But I am not certain that this will give the correct results.
0
Panagiotis ToumpaniarisSystem EngineerCommented:
The values in sheet 2 are not in sheet 1. Thus this won't work.
If you want to lookup values that actually exist you can use lookup like this :

=LOOKUP(2;1/(Sheet1!$A$2:$A$2501=Sheet2!E11)/(Sheet1!$B$2:$B$2501=Sheet2!F11);Sheet1!$C$2:$C42501)

Open in new window


If you want approximate matches replace the values in sheet 1 with approximation (using round).

Hope it helps.
Copy-of-grid-map-analysis-1.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob HensonFinance AnalystCommented:
Try this:

=INDEX(Sheet1!$C:$C,(MATCH($F11,Sheet1!$B:$B,1)-50+MATCH($E11,OFFSET(Sheet1!$A$1,MATCH($F11,Sheet1!$B:$B,1)-50,0,50,1),1)))

I notice that column B values are sorted in ascending order and are repeated 50 times with the same 50 values in column A repeated for each group of values in column B.

Thus, the first MATCH will find the group in column B that meets the largest value that is less than or equal to value y, by deducting 50 we then have the row of the start of that group.

The second MATCH then uses the start and finish of that group in an OFFSET function to find the closest to value x in that group.

The INDEX then uses the group and within group values to locate the value in column C.

For example:

x            y
297655      3149555

The MATCH on y value in column B gives row 1201, this is the last row of the group 3149547 (the next group is higher than required value). By deducting 50 we know that this group starts in row 1152.

We then use the OFFSET to create a range for rows 1152 to 1201 and match value x in this group, the closest result is 297648 which is in row 24 (of the group). We now know that the required value in column C is 24 rows after row 1152, row 1175 after allowing for headers.

Result:  2603.171

Hope that helps.
0
Rob HensonFinance AnalystCommented:
Would you be averse to re-arranging the data into a matrix format?

If that was OK, the INDEX formula could be simpler as you are only trying to find a column and a row; not a row within a group of rows:

=INDEX(Sheet3!$A$1:$AY$51,MATCH($E20,Sheet3!$A$1:$A$51,1),MATCH($F20,Sheet3!$A$1:$AY$1,1))

See attached with both options, sheet 3 has the matrix of values.
grid-map-analysis.xlsx
0
Rob HensonFinance AnalystCommented:
Matrix format would also potentially reduce the file size if that was to be an issue.

The list has over 7500 populated cells whereas the matrix only has 2500 because you don't have the repetition.
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
this is actually the sheet I want to use. I need to add in the NewWells sheet new x and y and lookup the value
GRID_To_XY_Value.xlsm
0
Rob HensonFinance AnalystCommented:
Sorry, don't understand the new requirement.

For the x and y values in NewWells sheet, where is the z value coming from, the existing XY list or the OFMGRID?

Will the values from NewWells list get inserted into the XY list?
0
Rob HensonFinance AnalystCommented:
See attached with my suggested formula in column G of NewWells sheet.

It looks like you already have the data in a matrix format, if that matrix sheet had headers for the rows and columns, you could lookup direct to it rather than the x y list; as per earlier file.
GRID_To_XY_Value.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShumsDistinguished Expert - 2017Commented:
Non of your combined value provided in sheet NewWells available, I have added possible matches with two values in yellow highlighted cells with below working formula:
=IFERROR(INDEX(XYandValue!C:C,MATCH(1,(NewWells!D7=XYandValue!A:A)*(NewWells!E7=XYandValue!B:B),0)),"Not Available")

Open in new window

Hope this helps.
GRID_To_XY_Value.xlsm
0
ShumsDistinguished Expert - 2017Commented:
You can even customize with Named Range.
Create 3 Named Range:
X_Values:
=OFFSET(XYandValue!$A$1, 0, 0, COUNT(IF(XYandValue!$A$1:$A$10000="", "", 1)), 1)

Open in new window

Y_Values:
=OFFSET(XYandValue!$B$1, 0, 0, COUNT(IF(XYandValue!$B$1:$B$10000="", "", 1)), 1)

Open in new window

Result_Values:
=OFFSET(XYandValue!$C$1, 0, 0, COUNT(IF(XYandValue!$C$1:$C$10000="", "", 1)), 1)

Open in new window

Finally enter below Array Formula confirmed with Cntrl+Shift+Enter and drag down:
=IFERROR(INDEX(Result_Value,MATCH(1,(D7=X_Values)*(E7=Y_Values),0)),"Not Available")

Open in new window

Check in attached...
GRID_To_XY_Value.xlsm
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
Very Good
0
Rob HensonFinance AnalystCommented:
Glad to be of help.

For completeness and future reference, might be worth explaining why you have chosen mine as your accepted solution.
0
Mohammed DallagPetroleum ConsultantAuthor Commented:
Dear Rob Henson,

Your solution was able to estimate the value within the chosen grid and that is what I want

Regards,

Dallag
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.