Creating a cross check between two sheets based on two separate inputs.

[NJT]

Hey Guys

Could you help me out with something. I am new to google sheets scripting and my coding is very rusty. I have a task that I am struggling with.

Basically I have two sheets. I need to take two inputs from sheet1 and find those inputs in sheet2 and then get an output from sheet2 that corresponds to the inputs from sheet1 and print the said result in a cell in sheet1. I will give a small example of what needs to happen. I don’t know if it can be done by a simple formula or if I need more of a script.

Basically I want to extract the green values from Sheet 2 based on the red and blue values and put them (green values) back into sheet 1. The blue values (IN#) are the primary and unique variables. Then need a check on the red secondary variables (CH#) and I need to get the corresponding green output (WO#).

Would anyone be able to help me with something like this?

Any help on this would be appreciated.

 

---

The information contained in this e-mail and any files transmitted with it is confidential and may be subject to legal professional privilege. It is intended solely for the use of the addressee(s).   If you are not the intended recipient of this e-mail, please note that any review, dissemination, disclosure, alteration, printing, copying or transmission of this e-mail and/or any file transmitted with it, is prohibited and may be unlawful.  If you have received this e-mail by mistake, please promptly inform the sender by reply e-mail and delete the material.  Whilst this e-mail message has been swept for the presence of computer viruses, eir does not, except as required by law, represent, warrant and/or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors, viruses, interception or interference.

eircom Limited, Registered as a Branch in Ireland Number 907674.  Incorporated in Jersey Number 116389.  Branch Address: 2022 Bianconi Avenue, Citywest Business Park, Dublin 24, D24 HX03, Ireland.

---

[Laurie Nason]

Hi,

I don't quite know how many rows & columns you would be working with in sheet 1 - but you could set up a helper column on Sheet 2 (ColG)which concatenates "Ch Type"&"In Numbers" so that then you can use a vlookup in sheet one to concatenate the IN1&CH2 to return the correct information from the "WO Numbers" - You will need however, to switch your columns about a bit in the VLOOKUP range to search - assuming Columns are starting from A in Sheets 1& 2 your vlookup in cell B2 in sheet 1 would look something like this:

VLOOKUP(B$2&$A2,{'Sheet 2'!$G:$G,'Sheet 2'!$A:$A},2,FALSE)

No code needed!

If you may have multiple WO numbers you'll need to use a filter expression in the cells in Sheet 1 and then surround that function with a TEXTJOIN option.

Unfortunately, it would require you to extend the function across all the cells in Sheet 1 where you need the info - I haven't worked out a way to do it using ARRAYFORMULAS, although I am sure there's a way - possibly SUMPRODUCT (but I am scared of looking at that particular function - sorry Ben, I know you blogged on it last week! Still scared :-))

If you really want a script to do it, it does get more complicated, but hey it would be a good learning curve too!

Hope it makes sense - if not get back to me and I'll try again!

Laurie

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a1782f50-0a06-4fc9-a599-329e07f7685fn%40googlegroups.com.

[NJT]

Hi Laurie. 

I'm not sure if the vlookup is going to work. because I need to look things up from the right column, rather than the left. secondly as far as i can tell vlookups work based on column numbers. But I need something that will check the column heading which would be a specific string and select that column based on a specific row heading. This is because the scaled up version of this sheet would have the data sorted regularly and the column headings might change if they decide to add a column someday and the row headings would definitely change most days according to new data entered.

But thanks for your help. I will look into the array formulas function.

Naveen

[Clark Lind]

I'm sure there are probably a few different ways of accomplishing this. 
One way is using a query with two comparisons.
Put this in cell B2 of Sheet1:
=QUERY(Sheet2!$A$1:$F$7,"select A where E = '" &B1& "' and F = '" &A2& "'",false)
The tricky part with queries is getting the concatenation right since the query string has to be in quotes.

[Bennett, Scott]

I have done something similar to this using index and Match.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bdcecd11-1ebb-4eaa-b40d-e70fcd8b24cdn%40googlegroups.com.

--

Scott Bennett

Data and Assessment Coordinator/Math Teacher

Bradley-Bourbonnais Community High School

P 815.937.3707 ext. 6010

[Zack Reynolds]

So vlookup in its native element is a bit rigid, but there are some cool ways you can use it, including to accomplish exactly what you're looking to do.

There is a YouTube video that explains all the various ways of using vlookup here: https://youtu.be/Vy1hX6okFh8

And the method you're looking for is at the 20:20 time mark.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bdcecd11-1ebb-4eaa-b40d-e70fcd8b24cdn%40googlegroups.com.

[Laurie Nason]

Hi - here's a sheet i knocked up to give you a starting point - it does what you asked in the original email without coding.

https://docs.google.com/spreadsheets/d/1sfpB3P89Ii7Keq0aPZ0ThzPyF7x-gaZLCmuO9BVE3EI/edit#gid=1776228330

HTH,

Laurie

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/57c9b669-25c8-4b40-9a99-3ae50a4c4150n%40googlegroups.com.

[NJT]

Hi Laurie.

Thank you so much for your help. I was able to adapt your formula to the scaled up version of my problem!

Naveen