Index-match function to return multiple values in a row or column

[bping]

I am trying to return the labels of values <500 but can only see the first label and not the remaining.

here is the formula I am using - =INDEX($B$2:$CO$2,MATCH(TRUE,B7:CO7<500,0))

Test.xlsx

I am not sure if I have the data setup correctly to do this or not.

Any help would be greatly appreciated!

Thanks

Bruno

[mcmahobt]

If I interpreted this correctly, paste into B21 and confirm with CTRL+SHIFT+ENTER. Then drag down through B32 and across through column CO.

Please Login or Register  to view this content.

[bping]

I don't really know what to say...I am speechless that it worked! Thanks for the help!

[bping]

I don't really know what to say...I am speechless that it worked! Thanks for the help!


Can I please have your input on the same thing else?


Basically, what I would like to do is starting with an excel file to figure out which barcodes (IonExpress xxx) have values <500
This is the starting file.
Attachment 376430

I did macro to first swap columns with rows (transpose) but I am not sure if needed to do this? Then highlighted the cells <500 and then try to identify the column headers of all of the <500 and build a function on those.

Do you think that it is possible to do all of this in one go? Let's say, I download the excel file and then run a macro and then get a report with all of the barcodes. I am not sure how to run the same macro on different files either.

Last question, how can I get to a level such as yours in Excel? Obviously your understanding of Excel's logic is amazing...I thought I had done a great job with my macro and all but I just realise that I might be overcomplicating things because Ido not understand the mechanics of it...

Thanks

Bruno

[mcmahobt]

Your attachment did not seem to upload correctly, could you try again?


As to increasing your level of Excel, stay on these forums long enough and some information is bound to stick.

[bping]

Project.xlsx

Hi there, here it is!

Thanks

B

[mcmahobt]

As you've said, I'm not sure why you need to transpose the data. Attached is the results again, done for the original workbook without the transpose.

[bping]

It looks great! Thank you very much! What is the best way to apply the same formula to other workbooks? Should I write a macro for it?

[mcmahobt]

You could go with VBA for it, but straight cutting/pasting may be just as easy if you have to work with a downloaded workbook every time.