Combine VLOOKUP, IF, AND in one formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have one worksheet with information for lots of people, but on separate
rows -

Name Date Type of Bill Cost
Joe Bloggs Jan 05 Gas £10
Joe Bloggs Dec 05 Electric £6
Jemma Jan 05 Gas £2
Jemma Jan 05 Electric £5

I want to add them to a new sheet, which is set out as:

Name GasDec04 GasJan05 ElectricDec04 ElectricJan05
Joe Bloggs 0 £10 £6 0
Jemma 0 £2 0 £5

Can I use a lookup to do this?

Thanks
 
Hi

would the following structure be acceptable:
.......................Dec 04........................Jan 05
Name.........Electric.....Gas..................Electric....Gas
Joe Bloggs...6.....................................................10
Jemma..................................................5.............2

if so, it can easily be achieved from your current data using a pivot table

click in your current data, choose data / pivot table and pivot chart report
NEXT
ensure that the whole range is selected NEXT
ensure new worksheet is selected and click LAYOUT
drag name to where it says "ROW"
drag date to where it says "COLUMN"
drag type of bill to where it says "COLUMN" ensuring it goes to the right of
date
drag cost to where it says "DATA"
click OK click FINISH

now right mouse click on Date and chose field settings, change automatic
subtotals to none
and you should have more or less what you're looking for.

-
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
 
I know how to use Pivot tables, but I want this spreadsheet to be able to
filter through to other spreadsheets, and update automaticallly.

Is there any other way?
 
I am adding this to an exsisting spreadsheet that already has information on
it, so I cannot use a Pivot table.

I am trying to use:

=IF(VLOOKUP(B2,'Sheet 1'!A1:F1262,5,FALSE)="Gas",AND,IF(VLOOKUP(B2,'Sheet
1'!A1:F1262,6,FALSE)="Dec-04",VLOOKUP(B2,'Sheet 1'!A1:F1262,2,FALSE)))
 
Hi

Let's the first table be on Sheet1 with headers in row 1, and second table
on Sheet2, also with headers on row 1 (both tables staring from column A).

As first step, modify either entries in Date column on Sheet1, or headers on
Sheet2, so date text are identical. I.e. or you have dates in form "Jan05"
etc., or your headers are like "GasDec 04".

Now, into cell B2 on Sheet2 enter the formula
=SUMPRODUCT(--(Sheet1!$A$2:$A$5=$A2),--(Sheet1!$B$2:$B$5=SUBSTITUTE(SUBSTITU
TE(B$1,"Gas",""),"Electric","")),--(Sheet1!$C$2:$C$5=SUBSTITUTE(B$1,SUBSTITU
TE(SUBSTITUTE(B$1,"Gas",""),"Electric",""),"")),Sheet1!$D$2:$D$5)

and copy the formula into range B2:E3
 
Back
Top