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!

 

[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

 

[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!