Match with 2 Lookup_Values used as search criteria.

[Sigmaz]

Hey all,
I'm trying to do a sort of query operation to get the apropriate data based
on two rows that need to be matched i.e. A1=Days of the week B1=Task 1 C1=Pay

so when I populate another spreadsheet with days in the column and tasks in
the A row I can get the pay for each cell associated with the day.
this is the formula I came up with but I can't seem to get it to work?

=OFFSET(A1:B1,MATCH("Thursday*" & "1",A2:A58:B2:B58,0),2) ?????

[Sigmaz]

I posted another message with a sample of the data, but it didn't post.. Le
me try to explain a little better...

Access queries a table and exports 3 columns into a new page in a spreadsheet.
Column A contains weekdays, Column B contains a code value represented by a
number from 1 to 8 Column C contains a numerical value that we use to
calculate on another sheet.

So with that explained.. what we have on another sheet is a grid of cells A2
to A9 are the code values and B2 to G2 are the days of the week. So it's a
grid of days and codes.. basiclly I need to have each cell call out tothe
exported data sheet and retrieve the Column C data meeting each cells
criteria.. so lets try this, forget about all the other cells, Lets say I'm a
cell looking to get the value for a certain code in Column B I need the
formula to check if Column A matches and if it does, Does coulmn B match
too, If that one does then return the data from C on the same row. So if I'm
looking for the data for Code 6 from monday I need to look thru the exported
data and find the two criteria then return the result in column C if all is
matched.

The exported data changes everyday and it is not uncommon to find monday
listed many times with other codes, so I need to be able to auto range the
search be cause I don't know wich row the data will end day to day so it's
like A1:???? .
I'm probably not wording this right.. I read some things before about this
kind of request But I cant follow it..HELP!






"Sigmaz" wrote:

> Hey all,
> I'm trying to do a sort of query operation to get the apropriate data based
> on two rows that need to be matched i.e. A1=Days of the week B1=Task 1 C1=Pay
>
> so when I populate another spreadsheet with days in the column and tasks in
> the A row I can get the pay for each cell associated with the day.
> this is the formula I came up with but I can't seem to get it to work?
>
> =OFFSET(A1:B1,MATCH("Thursday*" & "1",A2:A58:B2:B58,0),2) ?????
>

[GaryDK]

Hi Sigmaz,

Try adding a fourth column to the right of your data, entering the
following formula in cell D2 and copying it down -

=A2&B2

I would give column D a defined range name, for example, "DayCode".
Then, assuming Sheet1 contains your data, try the following formula in
cell B2 of your table -

=OFFSET(Sheet1!$C$1,MATCH(F$1&$A2,DayCode,0)-1,0)

which you should be able to copy to fill your table. (B2 may not be the
first cell. It may be B3 given your description.) If there isn't a
day/code combination for every day and code, you can use this if you
don't like to see #NA -

=IF(ISNA(MATCH(B$1&$A2,DayCode,0)),"",OFFSET(Sheet1!$C$1,MATCH(B$1&$A2,DayCode,0)-1,0))

Gary

[Biff]

Hi!

>A2 to A9 are the code values
>B2 to G2 are the days of the week.

Is B2:G2 supposed to be B1:G1?

Create dynamic named ranges for the data on the imported
data sheet.

Then you can use this formula entered as an array on your
other sheet starting in cell B2:

=INDEX(Values,MATCH(B$1&$A2,Weekd&Codes,0))

Values, Weekd and Codes are the named ranges for columns
A, B and C of the imported data sheet.

Copy across then down as needed.

If there is no matching data you'll get a return of #N/A.

If you don't want to see those use this formula:

=IF(ISNA(MATCH(B$1&$A2,Weekd&Codes,0)),"",INDEX
(Values,MATCH(B$1&$A2,Weekd&Codes,0)))

Biff

>-----Original Message-----
>I posted another message with a sample of the data, but
it didn't post.. Le
>me try to explain a little better...
>
>Access queries a table and exports 3 columns into a new
page in a spreadsheet.
>Column A contains weekdays, Column B contains a code
value represented by a
>number from 1 to 8 Column C contains a numerical value
that we use to
>calculate on another sheet.
>
>So with that explained.. what we have on another sheet is
a grid of cells A2
>to A9 are the code values and B2 to G2 are the days of
the week. So it's a
>grid of days and codes.. basiclly I need to have each
cell call out tothe
>exported data sheet and retrieve the Column C data
meeting each cells
>criteria.. so lets try this, forget about all the other
cells, Lets say I'm a
>cell looking to get the value for a certain code in
Column B I need the
>formula to check if Column A matches and if it does,
Does coulmn B match
>too, If that one does then return the data from C on the
same row. So if I'm
>looking for the data for Code 6 from monday I need to
look thru the exported
>data and find the two criteria then return the result in
column C if all is
>matched.
>
>The exported data changes everyday and it is not uncommon
to find monday
>listed many times with other codes, so I need to be able
to auto range the
>search be cause I don't know wich row the data will end
day to day so it's
>like A1:???? .
>I'm probably not wording this right.. I read some things
before about this
>kind of request But I cant follow it..HELP!
>
>
>
>
>
>
>"Sigmaz" wrote:
>
>> Hey all,
>> I'm trying to do a sort of query operation to get the
apropriate data based
>> on two rows that need to be matched i.e. A1=Days of the
week B1=Task 1 C1=Pay
>>
>> so when I populate another spreadsheet with days in the
column and tasks in
>> the A row I can get the pay for each cell associated
with the day.
>> this is the formula I came up with but I can't seem to
get it to work?
>>
>> =OFFSET(A1:B1,MATCH("Thursday*"
& "1",A2:A58:B2:B58,0),2) ?????
>>
>.
>

[Sigmaz]

Thanks Biff.
Actuallt B1 and G1 are headers dumped out by access so the datra starts
b2-c2 g2.....

I took another approach and this gets me the data but I';m having trouble
nexting the IF NA into the formula to surpress the #N/A and return 0's
instead...
any ideas?
Here's the new working formula.. How do I add the IF NA?
=INDEX(EXPORTSUMMARY!C2:C60,MATCH(1,(EXPORTSUMMARY!A2:A60=2)*(EXPORTSUMMARY!B2:B60=1),0))

Thanks for your help!



"Biff" wrote:

> Hi!
>
> >A2 to A9 are the code values
> >B2 to G2 are the days of the week.
>
> Is B2:G2 supposed to be B1:G1?
>
> Create dynamic named ranges for the data on the imported
> data sheet.
>
> Then you can use this formula entered as an array on your
> other sheet starting in cell B2:
>
> =INDEX(Values,MATCH(B$1&$A2,Weekd&Codes,0))
>
> Values, Weekd and Codes are the named ranges for columns
> A, B and C of the imported data sheet.
>
> Copy across then down as needed.
>
> If there is no matching data you'll get a return of #N/A.
>
> If you don't want to see those use this formula:
>
> =IF(ISNA(MATCH(B$1&$A2,Weekd&Codes,0)),"",INDEX
> (Values,MATCH(B$1&$A2,Weekd&Codes,0)))
>
> Biff
>
> >-----Original Message-----
> >I posted another message with a sample of the data, but
> it didn't post.. Le
> >me try to explain a little better...
> >
> >Access queries a table and exports 3 columns into a new
> page in a spreadsheet.
> >Column A contains weekdays, Column B contains a code
> value represented by a
> >number from 1 to 8 Column C contains a numerical value
> that we use to
> >calculate on another sheet.
> >
> >So with that explained.. what we have on another sheet is
> a grid of cells A2
> >to A9 are the code values and B2 to G2 are the days of
> the week. So it's a
> >grid of days and codes.. basiclly I need to have each
> cell call out tothe
> >exported data sheet and retrieve the Column C data
> meeting each cells
> >criteria.. so lets try this, forget about all the other
> cells, Lets say I'm a
> >cell looking to get the value for a certain code in
> Column B I need the
> >formula to check if Column A matches and if it does,
> Does coulmn B match
> >too, If that one does then return the data from C on the
> same row. So if I'm
> >looking for the data for Code 6 from monday I need to
> look thru the exported
> >data and find the two criteria then return the result in
> column C if all is
> >matched.
> >
> >The exported data changes everyday and it is not uncommon
> to find monday
> >listed many times with other codes, so I need to be able
> to auto range the
> >search be cause I don't know wich row the data will end
> day to day so it's
> >like A1:???? .
> >I'm probably not wording this right.. I read some things
> before about this
> >kind of request But I cant follow it..HELP!
> >
> >
> >
> >
> >
> >
> >"Sigmaz" wrote:
> >
> >> Hey all,
> >> I'm trying to do a sort of query operation to get the
> apropriate data based
> >> on two rows that need to be matched i.e. A1=Days of the
> week B1=Task 1 C1=Pay
> >>
> >> so when I populate another spreadsheet with days in the
> column and tasks in
> >> the A row I can get the pay for each cell associated
> with the day.
> >> this is the formula I came up with but I can't seem to
> get it to work?
> >>
> >> =OFFSET(A1:B1,MATCH("Thursday*"
> & "1",A2:A58:B2:B58,0),2) ?????
> >>
> >.
> >
>