If A1 is present in column B, get C. How do I make it?

I have â€śCarâ€ť in a sheet, on another sheet I have two columns with â€śVehicleâ€ť (Car, Bike, Train, etc) and one list with â€śWheelsâ€ť (4, 2, 34, etc).

Iâ€™d like the sheet to give me 4 in the cell next to â€śCarâ€ť.

Iâ€™m new to sheets. Thanks if you will help. Happy holidays.

You could do that with a vlookup

If you have header in line 1.
Use this method - in cell b2:
=arrayformula(if(a2:a<>"","",c2:c))

1 Like

They are not in the same sheet so the correspondant of A2 in one sheet could be B16 and not B2

in sheets? How?

replace withâ€¦

YourSheetName!C2:C

I actually learned a great deal from this post by @ThinhDinh

There are some really great tips in here and best practices to follow.

Vlookup worked, thanks.

Should arrayformula have worked too?

1 Like

Since Iâ€™m here, how do I apply a formula to the whole columns including future new rows?

Here is an example with the formula starting in the header row.

``````={"Yourheaderhere";arrayformula(iferror(VLOOKUP(A2:A,NamedRange,2,false),""))}
``````

Note: Donâ€™t forget the } at the end of the formula

3 Likes

Thx a lot, it works.

So arrayformula is a powerful magic i supposeâ€¦

2 Likes

Yes the array formula is very powerful. Itâ€™s always best to start in the header rows and to use iferror or if(isblank type logic when building your formulas.

1 Like

Iâ€™m now trying other formulas I need to apply to whole columns but it only does it for the first row:
If I add the G2:G; etc, i get all the joins in one cell.

However, itâ€™s late here, and these things are mind energy expensive ^^ thx again, to the next one!

Maybe try to use concatenate function instead?..this is a little above my pay grade

Is there any reason for not doing any of this within the Glide data editor with relations, lookups, templates, etc?

2 Likes

The only reason I ever use vlookups is for data sets that have too many rows for us to attach to glide.

Other than that Jeff makes a good point here.

2 Likes

Seems like it should be

``={â€śHeaderâ€ť;arrayformula(IF(A2:A="";"";G2:G&" - "&E2:E&" - "&F2:F&" - "&W2:W&" - "&O2:O&" - "&C2:C&" - "&D2:D))}``
1 Like

I thought I wanted to keep the data in sheets, and learn how to do it if Iâ€™d need it, and I thought I might have needed additional columns in glide, and I have way too many.

And, most of all, I canâ€™t convert a normal column into a glide column, so now if I want to use the newly made one, I will have to manually go and change it in ALL of the parts of the app that use that column, can you confirm that for me or is there a way to save me from this?

No, thatâ€™s fine. Thereâ€™s nothing wrong with doing any of that in the google sheet if you need it there and thatâ€™s what you prefer. I just wanted to make sure that you were aware of the option.

In my opinion, setting some of that stuff up with Glide columns is easier, more flexible, and future proof compare to writing formulas. Also glide columns will give you instant results instead of waiting for data to sync from the app to the glide servers, then to the sheet, run through the formulas, then sync back to the glide servers, then finally sync back to the app. Itâ€™s usually only a few seconds, but itâ€™s not instant like glides computed columns which happen directly on the device itself. Itâ€™s just better to consider your options sooner rather than later when your app gets larger and more complex.

My app was built before there was even a glide data editor, so a lot of it originally used a lot of arrayformulas, queries, and other google sheet logic. Iâ€™ve slowly been converting to glides computed columns, which has sped things up a lot, but itâ€™s very tedious to switch components over to new columns. So, I understand if you donâ€™t want to mess with any logic you already have in place.

Again, just making sure you were aware of the option.

2 Likes

Ok, thx for the information, thatâ€™s appreciated.
Will keep that in mind.

1 Like