Help

[Tom Weston]

Hello
I have a sheet which contains a list of items in col A and the number of
items in col B, call this my 'spares on the shelf' sheet.
I have another sheet with a list of items in col A and the number of items
in col B, call this my 'installed plant' sheet.
I would like to create a sheet which contains the 'installed plant' list and
quantity, then automatically check through the 'spares on the shelf' sheet
for a name match and insert the 'spares on the shelf' quantity from col B
into the new sheet and calculate the percentage of spares on the shelf for
each match.
If there is no name match then the comparison should carry on to the next
item on the 'installed plant' sheet.
I cann't work out how to do it in excel, maybe it's not possible.

I would like to end up with something like:

name, installed, spares available, percentage
w1 , 2, 1, 50%
w2 , 4, 0, 0%
w3 , etc

 

[Dave Peterson]

If your names are unique on each sheet, you might be able to use a worksheet
formula: =Vlookup().

It's used to search the leftmost column of a table, find a match, and then bring
back whatever column you specified.

For instance, say your spares were on a worksheet named spares.

You could put something like this in C2 of the main sheet:

=vlookup(a2,spares!$a$1:$c$9999,3,false)

to return the third column over for that first exact match.

Then you could use what was returned in your percentage formula.

If the match isn't found, you'll get #n/a back. To hide this error:

=if(iserror(vlookup(a2,spares!$a$1:$c$9999,3,false)),0,
vlookup(a2,spares!$a$1:$c$9999,3,false))
(all one cell.)

 

[Tom Weston]

Great, it does exactly what I needed.

Tom

If your names are unique on each sheet, you might be able to use a worksheet
formula: =Vlookup().

It's used to search the leftmost column of a table, find a match, and then bring
back whatever column you specified.

For instance, say your spares were on a worksheet named spares.

You could put something like this in C2 of the main sheet:

=vlookup(a2,spares!$a$1:$c$9999,3,false)

to return the third column over for that first exact match.

Then you could use what was returned in your percentage formula.

If the match isn't found, you'll get #n/a back. To hide this error:

=if(iserror(vlookup(a2,spares!$a$1:$c$9999,3,false)),0,
vlookup(a2,spares!$a$1:$c$9999,3,false))
(all one cell.)