Calling experienced excel users

Things Help
381

The INDEX/MATCH function in Excel is used to lookup and retrieve data from a table. It is an alternative to the VLOOKUP function and is more flexible in terms of the columns it can return data from.

The INDEX function returns a value or reference to a cell based on its position in an array. The MATCH function returns the relative position of a value in an array.

Together, these functions can be used to find and retrieve data based on a specific criteria. The syntax for the INDEX/MATCH function is:

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))

Where:

  • array is the range of cells you want to return the value from
  • lookup_value is the value you want to find in the lookup_array
  • lookup_array is the range of cells you want to search for the lookup_value
  • match_type is an optional argument that specifies how the match should be performed (0 = exact match, 1 = closest match, -1 = closest match that is less than the lookup_value). If this argument is omitted, it defaults to 1.
1 Like
382

Colm all the way. Col-yoomers give me ire.

383

@Ceist

I have % in column D that are colour coded based on conditional formatting for value

In column C is the cash value relating to this %

Please write a formula that will
Count the number of items in each colour colum d
Sum the value of items in each colour in column c

384

Sure, you can use the SUMIF and COUNTIF functions to achieve this. Here is the formula you can use:

For counting the number of items in each color column D:

=COUNTIF(D:D, "color criteria")

For summing the value of items in each color in column C:

=SUMIF(D:D, "color criteria", C:C)

Replace “color criteria” with the criteria you used for conditional formatting in column D. You can use the above formulas for each color criteria used in column D to get the count and sum of values in column C for each color.

1 Like
385

@Ceist is there a way I can generate a random alphanumeric string of 7 numbers followed by 2 letters in a column in Excel? Each value should be unique

386

Yes, you can use a combination of the RAND and CHAR functions along with a formula to generate a unique random alphanumeric string in Excel. Here’s the formula you can use:

=LEFT(CONCATENATE(RANDBETWEEN(0,9),RANDBETWEEN(0,9),RANDBETWEEN(0,9),RANDBETWEEN(0,9),RANDBETWEEN(0,9),RANDBETWEEN(0,9),RANDBETWEEN(0,9),CHAR(RANDBETWEEN(65,90)),CHAR(RANDBETWEEN(65,90))),9)

This formula will generate a random alphanumeric string with 7 numbers followed by 2 letters. The RANDBETWEEN function generates random numbers between the specified range, and the CHAR function converts the random number to a corresponding character. The LEFT function is used to extract the first 9 characters of the concatenated string, which is the alphanumeric string you want.

You can copy this formula down to generate unique alphanumeric strings for each row in a column.

1 Like
387

Unreal thanks a million

388

You’re welcome! Let me know if you need any further assistance.

389

Have a fairly basic spreadsheet in use
All hard coded data nothing linked.
One formula in it adding the amount in 5 columns
I want to sort the answers largest to smallest

A fairly basic task all told

When I try to sort largest to smallest however I am getting an error message “you can’t change part of an array”

Iv asked a succession of geeks at work and none of them can figure out what is gone wrong

Any clues?

390

Could you copy that column but then select “paste values” into a new column, see if you can then sort that column.

391

Tried that didn’t work. Tried c and p all the detail into another sheet and that didn’t work either

One of the fellas who can generally fix most things for me says it’s a glitch which is just a code for him not having a clue I’m sure

392

Is there actually an array formula (ie with curly brackets that you need to press Ctrl Shift and Enter to refresh)

393

Nope. Simple =sum(e2,g2,k2)

394

Sounds like somebody may have created an array inadvertently.

If not, you could create another row with the numbers in order: =large(cell range, 1) for largest, =large(cell range, 2) for second largest etc.

395

Did you try copy and paste all values only into another sheet?

What columns have you selected to sort by? Is it all or just the column you want to sort by?

396

I’m the only person with access but I may have …iv no idea what an array is

397

Yes

398

I just want to sort data in one col but obv want the corresponding detail to follow it in the row

399

Try get rid of the “sum” if it’s not rows beside each other. Just do basic =E2+G2+K2

And make sure you are sorting by all columns in the table, as in all have drop down options available for each column and not just the one you want to reorder by.

400

Tried that too