How to Select a value from price sheet based on two values of the primary

PeteM_1972

Hi all,

I have pricing data based per client, so I have a sheet for each client. My engineers complete a simple form that posts data to my primary data sheet.

For the back office, I want to automate the pricing based on:

Client
Parts used

I can vlookup the parts simply enough based on the parts, however I cant seem to get the IF / Then to use the Client to select the correct sheet.

In old money:

IF (Client=AAA, then vLookup COLUMN_Part from sheet AAAPrice Range, 2, false); else "Nope"

I did think that I can use a single sheet for all pricing and vlookup based on client and part to a single sheet, but my head has fried and I need a lay down……lol

Find more posts tagged with

Accepted answers

KPH

If you only have 2 "special clients" you can use an IF function before the INDEX COLLECT.

IF special client 1 the COLLECT range would be the client 1 price column, if client 2, the COLLECT range would be the client 2 price column, otherwise the normal price column. Something like:

=IF(client@row="client 1",INDEX(COLLECT({client 1 price column},{part column}, part@row),1),IF(client@row="client 2",INDEX(COLLECT({client 2 price column},{part column}, part@row),1),INDEX(COLLECT({normal price column},{part column}, part@row),1)))

All comments

KPH

Hi @PeteM_1972, get up!

Your final thought before the brain fry is probably the right approach IMO. One sheet, three columns:

part
client
price

Then instead of VLOOKUP used INDEX and COLLECT (which is more robust and more powerful)

You can collect the price where the part and the client match.

Your formula will look something like

=INDEX(COLLECT({price column},{client column},client@row,{part column}, part@row),1)

Would that work, or do you have too many clients to make this viable?

PeteM_1972

Ah, yes…. I think this could work. I've since created a single sheet for pricing as I realised I have 2 "special clients" and everyone else is in a standard bucket.

Part No, Part Desc, Client 1, Client 2, Everyone Else

Main data sheet is created from the engineers job form and among other things they collect:

Part Desc, Client Name, serial number, etc, etc

So, for this query to work, I would have to change the parts sheet as I can't select the three price columns and get the output to work.

My new logic is: =INDEX(COLLECT({Pricing Range 1},{Pricing Range 2},client@row,{Pricing Range 3}, part@row),1)

"Pricing range 1" is the three price columns (Client A, Client B, Standard)

"Pricing range 2" is the three price column titles (Client A, Client B, Standard)

"Pricing range 3" is the parts columns (Parts Desc)

Clearly this is too complex as the query can't decide which of the 3 columns to use as the client name is the column name and the price is the row.

KPH

If you only have 2 "special clients" you can use an IF function before the INDEX COLLECT.

IF special client 1 the COLLECT range would be the client 1 price column, if client 2, the COLLECT range would be the client 2 price column, otherwise the normal price column. Something like:

PeteM_1972

Query as written is:

=IF(Client@row="EOE",INDEX(COLLECT({Pricing Range 1},{Pricing Range 2}, part@row),1),IF(Client@row="Dial-a-Ride",INDEX(COLLECT({Pricing Range 3},{Pricing Range 2}, part@row),1),INDEX(COLLECT({Pricing Range 4},{Pricing Range 2}, part@row),1)))

I get "#UNPARSEABLE" as the result

Range 1 is the Price Column for EOE

Range 2 is the Part Column

Range 3 is the Price Column for Dial-a-Ride

Range 4 is the Price Column for standard clients

KPH

Query looks good. Can you take a screenshot of it as if you were editing it so I can see the colors? It should look like this (this is just for illustration purposes - I haven't set up cross sheet references)

PeteM_1972

All fixed. I forgot to change the parts column name to "part" in both the engineers form and the pricing sheet.

Now works perfectly. Thanks @KPH

KPH

Wonderful!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.