Is it Hlookup, Match function or something else?

[Anjt87]

Hi guys,
I have a spreadsheet exported from from Access, where I have a "False" or "True" cell values which relate to a Country name (If Destination is Costa Rica it's "True"). Now I'm trying to create a formula which would work, if Value is "True" in a range of cells (C4:AG4), then give a Name of the country which can be found in (C3:AG3 or should it be C5:AG5? as lookups don't work if table array is under range lookup).
How do I do approach it?

 

[Jonmo1]

Welcome to the board.

It's difficult to tell exactly, because the picture conflicts with your description.

But anyway, you can use Index and Match

=INDEX(C$1:AG$1,MATCH(TRUE,C4:AG4,0))

That will return the value (country name) from Row 1, based on where the TRUE is found in Row 4.

 

[Anjt87]

Great it worked. Thanks!

 

[Anjt87]

A bit different scenario. If there is more than one country to show because there is more than one "TRUE" value (as the product is multi-destination) is it possible to show all results in one cell?
Cheers

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[Jonmo1]

In that case, I would transpose the data, then you can use autofilter on each product to search for TRUE's

Highlight and copy the entire range
Go to a new blank sheet, and select A1
Then Right Click - Paste Special - Transpose.

Now you have the products listed accross row 1, whit the countries listed down the column
And you can apply the Autofilter, to filter on a specific product to show all the TRUE's.

 

[Anjt87]

That would work if only I wouldn't have over 1000 products . Is there less manual way?

 

[Jonmo1]

Perhaps a Pivot Table