google sheets formula | C2C Community
Solved

google sheets formula


Hi there, from Azores Islands! i need some help in a formula .

for example :

i have 3 columns , column A with 20 possible text codes , B with 70 possible number codes and C with 35 price values.

Once i type, in another cell , one text code that exists in column A, and in another cell ,one number code that exists in column B , i want to be returned a specific price value in another cell.

 

hope could get some help.

 

thanks ,Jason

icon

Best answer by alan.muntadas 27 April 2022, 15:50

View original

18 replies

Userlevel 7
Badge +29

Hello from Crete island, @AZORES !

 

So, to understand more, you have 3 columns: A, B and C

Column A has possible text codes (for instance: A2 has “shoe”)

Column B has possible numeric codes (for instance: BS has “48”)

Column C has price values (for instance: C2 has “110”)

 

So, when you type in cell D2 “shoe” and in E2 “48” then you want to automatically get the price that was set in C2 for these two values? The price values are somehow set in column C to reflect the combinations of A and B columns?

Can you give a more detailed example? 
And by the way, welcome to C2C! :) 

Hi Dimitri ,

 

So we are both blessed , in this days, sorrounded by sea !😀

 

That's right ! That's exact what i need .

Userlevel 7
Badge +29

Hi Dimitri ,

 

So we are both blessed , in this days, sorrounded by sea !😀

 

That's right ! That's exact what i need .

 

Perfect! :D

A2 has B2 next to it and C2 as a price that set’s these two. But if you type in A4 and B8, what price from C Column is associated with the result? How do you set this relation of A and B elements?

I have a schedule with the prices of each.

 

When i type on a diferent cell , One example of column A and on another cell ,One example of column B, i want the price to correspond with the One from the schedule that i bilt.

Userlevel 7
Badge +29

I am not sure I understand 100% what you mean with *the schedule* that you have built and how you have built it.

I will also call @chanelgreco  and @alan.muntadas and @dominikkugelmann to the rescue! Perhaps you can understand something more and have a solution? :) 

Userlevel 5
Badge +9

I don’t quite understand the goal. But maybe have a look at the IFS formula in Google Sheets: https://support.google.com/docs/answer/7014145?hl=en

Userlevel 7
Badge +65

@AZORES have you checked @chanelgreco’s answer?

It helps you?

Hi there ,

I'll try too explain better .

 

In Google sheet , i have a schedule(table),

 

3 Columns, A ,B and C and 6 row (1-6)

 

Column A its composed by the symbols:

ALC

NVG

PC

MM

XF

PJ

 

Column B its composed by ALPHANUMERIC:

PT654783456

PT654783456

PT232896700

PT232896700

PT564783422

PT564783422

 

Column C values 

4,32

4,10

4,50

478

3,59

3,65

 

In same Google sheet i have another schedule(table ) , empty.

3 Columns D,EF and 6 rows (1-6)

I would like , when i type on column D , One of the symbols of column A , and type on column E One of the ALPHANUMERIC of column B,that the corresponding value of column C emerge automaticly in column F.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Userlevel 7
Badge +29

Okay, what if I type in Column D one of the symbols of Colymn A, say A2: ALC

Then I type in column E one of the alhpanumeric of column B, say B5: PT232896700

Then in column F, which one is the "corresponding" value? How does this association happen so that it indeed becomes corresponding to the previous 2 values?

 

For A2 (ALC) and B2 (PT654783456) let's say that the corresponding value is 4,32. Good.

But for A2 (ALC) and B3 (PT654783456), how do we know what the corresponding value is? Is it C2 (4,32???) or C3 (4,10???)? Or any other?

How does this relation come up so that we have a "corresponding" value? :)

 

If it was that C relates to A+B = C then ok. Then it would be ALC + PT232896700 = only 4,32 but if it is a different combination from different rows, then we get an… error? Or is there some logical process?

 

That’s what I don’t undertand in your problem in order to help you with an answer :)

Userlevel 5
Badge +9

Ok, what you need to do is use the VLOOKUP formula. But you need to tweak it as you aren’t only looking for one key but instead two keys.

So the formula would look something like this:

=VLOOKUP(D1&" "&E1,A1:C6,3)

 

I got the idea from the Google Sheets guru Ben Collins: 

https://www.benlcollins.com/spreadsheets/vlookup-multiple-criteria/

Ok i have tried.

It returns a diferent value price (example  it should return 4,50 and its shown 4,00)and its is shown short after i type de Code from column A. It doesn't need the ALPHANUMERIC to be typed for return of value .

Userlevel 5
Badge +9

Well then, I guess it doesn’t solve your problem. 

So that's it?

It doesn't have solution ?

Userlevel 7
Badge +29

I will also call @PaulRLees in case he has any other idea? 
@dominikkugelmann , @alan.muntadas perhaps?

Userlevel 5
Badge +11

you need to create a double filtre.

You can use the Query funtion with the necessary select etc..

or you can create dependant drop downs

 

here is a good tuto (in french on the second option).. but easy to follow the logic..

https://www.youtube.com/watch?v=8HFngLvLj9k&t=443s

tank you Alan and everyone that´s helping! i will try it.

ok, that´s not what i need Alan :/

 

I'll try too explain better .

 

In Google sheet , i have a schedule(table),

 

3 Columns, A ,B and C and 6 row (1-6)

 

Column A its composed by the symbols:

ALC

NVG

PC

MM

XF

PJ

 

Column B its composed by ALPHANUMERIC:

PT654783456

PT654783456

PT232896700

PT232896700

PT564783422

PT564783422

 

Column C values 

4,32

4,10

4,50

478

3,59

3,65

 

In same Google sheet i have another schedule(table ) , empty.

3 Columns D,EF and 6 rows (1-6)

I would like , when i type on column D , One of the symbols of column A , and type on column E One of the ALPHANUMERIC of column B,that the corresponding value of column C emerge automaticly in column F.

anytime i type information that´s it´s not in the first schedule (table) in comes a error, correct?

 

that´s ok because i will now that´s something it´s not rigth.

 

i thougth it was something like “if”, “and” “or “ function 

 

Good afternoon,

It's being pretty dificult to get my problem Solved ....

Reply