You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: ric frankland
ric frankland Author
User level: Level 2
380 points

xlookup over two different tables

hello, so I think i may have taken on something way beyond my level...!


below is a much simplified version of two tables that i'm working with, as a screen shot, plus a link to the Numbers file.


i have different windows (numbered 1-5), each with different frame types (a to e)... with each type consisting of different materials (ONE and TWO) and different thicknesses (in mm).


i'm trying to create a formula that will lookup the frame type and then lookup what materials are being used, then work our how much of each material i need based on the length or width of the window.


so for example, the result i'm trying to achieve for window 2 is:


6000mm of 15mm of material ONE

?mm of 25mm of material ONE

?mm of 30mm of material TWO

?mm of 200mm of material TWO




😳


cheers


Ric



[Link Edited by Moderator]

Posted on Jan 21, 2023 10:01 AM

Reply
3 replies
User profile for user: ric frankland
ric frankland Author
User level: Level 2
380 points

Jan 22, 2023 2:58 AM in response to Barry

cheers Barry, yes, when i started writing the post i realised that it was difficult to ask for help if i couldn't even work it out with paper and pencil... which i was even struggling with!


totally with you on focusing on the process... but after a good night's sleep, i think i cracked it... i just need to add some more components/data and then double/triple check it... but here's a screen grab from my actual spreadsheet of a formula that seems to work:


Reply
User profile for user: Barry
Barry
User level: Level 9
59,957 points

Jan 21, 2023 6:52 PM in response to ric frankland

Hi Ric,


Perhaps over simplified.


Which is "Table 2"?

I'm assuming it is the larger table with the error triangles.

Could you describe the calculation as you would do it with paper and pencil to obtain the expected results for Window 2.

My interest is more on the process, sources of data used in the calculation, and units applicable to the data and to the results than in the actual result.


Regards,

Barry


Reply

xlookup over two different tables

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up