What syntax would I use if I want a formula to select a value from a separate table?

Question

Level 1

17 points

What syntax would I use if I want a formula to select a value from a separate table?

MY USE CASE:

I have a table to track share purchases in one company (let's use GM as an example). To track accrued gain / loss I inputted a formula that would calculate the current value by selecting a cell (D2) that I have in a header row where I would type in the share price for GM. By using the 'preserve column' and 'preserve row' feature aka syntax $D$2 I could have multiple rows (one for each purchase made at different times) and each distinct purchase would have its own accrued gain / loss. I used to have one table for each company but I have found it better to use one table for multiple companies, so now of course my formula will not work because some rows require the price of GM and other rows require the price of a different company.

MY INQUIRY:

How do I write a formula that will 1) look in that row in column B (where I have the company ticker symbol) to see what company it is (GM or AAPL, etc) and then 2) reference a separate table to look up the price for that company by searching that separate table for the row that contains the ticker symbol? In that separate table I will have column A for ticker symbol and column B for price, so the formula would need to find the ticker symbol in column A and then take the price from column B.

Thanks to all who reply.

Posted on Feb 11, 2022 11:06 AM

Reply

Answer 1

Top-ranking reply

Barry

Level 9

59,951 points

Mar 4, 2022 2:49 PM in response to RWforumID777

Hi Rod,

Here's the formula suggested by Jason, and below it, a copy of your formula.

jaxjason: XLOOKUP($A2,Prices::A,Prices::B,"Not Found",0)

Rod: XLOOKUP( CPA::A,CPA::B, "Not Found",0)

Text version of your formula uses "CPA" as a stand-in for the name of the table containing the prices, and column letters in place of the column label.

The space between XLOOKUP( and 'CURRENT…' should contain $A2,

This argument tells XLOOKUP what it is looking for.

SG's formula differs from Jason's only by the omission of the last argument: ,0

This is an optional argument that tells XLOOKUP to accept only an exact match of the search value. Omitting that argument has the same effect.

Looks like SG has a handle on your most recent post.

Regards,

Barry

Reply

Answer 2

jaxjason

Level 5

5,047 points

Feb 13, 2022 12:16 PM in response to RWforumID777

I think I was able to recreate what you are implying I made a holdings table with symbol in A and a

Prices Table with Column A being Symbol and B being price. That gives me an Xlookup that looks like this:

XLOOKUP($A2,Prices::A,Prices::B,"Not Found",0)

Jason

Reply

Answer 3

RWforumID777 Author

Level 1

17 points

Mar 4, 2022 8:54 AM in response to SGIII

Hello SG!

That worked perfectly, thank-you! I have the prices brought into my spreadsheet as I was hoping, but after that I hit a roadblock; perhaps you can give some insight on how to solve the issue. I am trying to calculate the 'Accrued Gain / Loss' by subtracting as follows: "Current market value - Amount invested". The idea is that the formula will give me a result only if my transaction is "Status" code 1, 2, or 3 which relates to my various stages of buying / holding, but if the "Status" is code 4 that means I've sold the investment and therefore "accrued gain / loss" is irrelevant and should not be calculated. I can't figure out a way to create the right syntax that will do what I want without some kind of error. I attempted one method where it works for status codes 1-3 but gets an error if the status code is 4, then I found a method to make it work for status codes 1-4 but quickly found out that the formula gives errors if the row is blank. Included in my post are screenshots showing:

My two tables, one which brings in the prices, and the second one where I put my transactions which includes the price import (thanks again for showing me how to do that in your prior response)
Screenshots that show 3 different formulas I have tried and the error from each. You will be able to follow which is which because I colour coded the rows in the screenshots so that formula is shown by the colour-coded row and so is the error.

If you can point me in the right direction or give me some ideas on how to solve the issue that would be great. The goal is to tell the sheet to calculate 'accrued gain/loss' only when 'Status' is 1-3 and otherwise do nothing, including to not produce an error in a blank row.

Thanks SG,

Rod

Reply

Answer 4

RWforumID777 Author

Level 1

17 points

Feb 14, 2022 10:27 AM in response to jaxjason

Hello Barry & Jason,

Thanks to both of you for your replies.

I have mocked up an example of what I am hoping to achieve and based on both your input I tried an XLOOKUP formula but got a formula error. See examples:

Reply

Answer 5

SGIII

Level 8

36,134 points

Feb 23, 2022 3:43 AM in response to RWforumID777

Try this:

=XLOOKUP($A2,CURRENT PRICES INPUT::A,CURRENT PRICES INPUT::B,"Not Found")

You can try copy-pasted the formula above into the cell.

If you enter it yourself be sure to let the formula editor do most of the work for you. You click a cell or column and it enters the reference for you.

And remember you can always look up more information about XLOOKUP or other functions, with lots of helpful examples, at Help > Formulas and Functions Help in your menu, or the function browser that appears in the right pane when you are entering a formula.

SG

Reply

Answer 6

SGIII

Level 8

36,134 points

Mar 4, 2022 12:41 PM in response to RWforumID777

RWforumID777 wrote:

The goal is to tell the sheet to calculate 'accrued gain/loss' only when 'Status' is 1-3 and otherwise do nothing, including to not produce an error in a blank row.

I think this will do what you want:

Entered as:

=IF(AND(A2>0,A2<=3),F2−H2,"")

Make sure you don't have invisible "blanks" or "spaces" in column A.

SG

Reply

Answer 7

Barry

Level 9

59,951 points

Feb 11, 2022 5:41 PM in response to RWforumID777

The project is essentially a LOOKUP situation, but the description of what to to be entered and what is to be looked up is not clear.

"How do I write a formula that will

1) look in that row in column B (B2?) (where I have the company ticker symbol) to see what company it is (GM or AAPL, etc) and then

2) reference a separate table to look up the price for that company by searching that separate table for the row that contains the ticker symbol?

Will there be more than one row that contains GM (or any other ticker symbol)

Is "the price for that company" the purchase price for your investment in that company?

In that separate table I will have column A for ticker symbol and column B for price, so the formula would need to find the ticker symbol in column A and then take the price from column B."

Can you do a mockup of the two tables, protecting your information by using random 'prices' and "Comp1, CPA; Comp2,CPB, etc for tthe company names and ticker symbols?

That would provide a visual model of the document you are working on and would help in making any suggested formulas fit your model.

Regards,

Barry

Reply

Answer 8

RWforumID777 Author

Level 1

17 points

Feb 22, 2022 8:34 AM in response to Barry

Hello Barry,

I posted an example of what I'm trying to achieve, see my example with the 'stocks purchased' and 'current prices input' table. I'd appreciate it if you could have a look and give me your insight. - Thanks, Rod

Reply