Lookup help, multiple columns

[texasduckhunter]

I'm unsure what formula I need to return an adjacent value to the min value from a set of columns...

My excel knowledge is very limited

I have data in cells BC20 to CJ20 and down to BC136 and over to CJ136

I have numbers in every other column and text in between

example:
BC20 has pricing
BD20 has lead time
BE20 has pricing
BF20 has lead time
and so on

I have a formula in CK20 to pull min value, im sure this is probably not the easiest way but its all I know [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=MIN(BC20,BE20,BG20,BI20,BK20,BM20,BO20,BQ20,BS20,BU20,BW20,BY20,CA20,CC20,CE20,CG20,CI20)

In column CL i pull in the vendor information from the column titles
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=INDEX($BC$19:$CB$19,MATCH(CK20,BC20:CB20,))

Those seem to work great

But my issue is that i need the corresponding lead time to pull into CM from the Min value i have in CK

HELP PLEASE [/FONT]_{^{<strike>
</strike>}}[/FONT]

 

[DanteAmor]

Try this

In the CK20 cell you can put the array formula

=MIN(IF(ISODD(COLUMN(BC20:CJ20)),BC20:CJ20))

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself


--------------
in CM20:

=INDEX(BD20:CJ20,MATCH(CK20,BC20:CI20,0))

 

[texasduckhunter]

when i do that it puts nothing in CK and in CM it pulls my min,

i need to note that some columns have no values because theres no price from vendor

 

[DanteAmor]

when i do that it puts nothing in CK and in CM it pulls my min,

i need to note that some columns have no values because theres no price from vendor

Try this en CK20

=MIN(IF(ISODD(COLUMN(BC20:CJ20)),IF(BC20:CJ20<>"",BC20:CJ20)))

Is an array formula to accept must be press Shift + Control + Enter.

 

[texasduckhunter]

That seems to have done the trick!

Thank you very much!

 

[DanteAmor]

That seems to have done the trick!

Thank you very much!

I'm glad to help you. Thanks for the feedback.

 

[texasduckhunter]

DanteAmor,

you did so well the last time, i tried copying what you did with the following adjustments and it didnt work:

I have data in cells CP20 to DW20 and down to CP135 and over to DW135

I have numbers in every other column and text in between

example:​

CP

20 has pricing​

CQ

20 has lead time​

CR

20 has pricing​

CS

20 has lead time​

and so on

i need the same kind of help please.
​

 

[DanteAmor]

DanteAmor,

I have data in cells CP20 to DW20 and down to CP135 and over to DW135

​

=MIN(IF(ISODD(COLUMN(CP20:DW20)),IF(CP20:DW20<>"",CP20:DW20)))

Is an array formula to accept must be press Shift + Control + Enter.

 

[texasduckhunter]

there are some cells with no data, how does it skip those?

 

[DanteAmor]

there are some cells with no data, how does it skip those?

Example:

2 Blank 3

What's the result? 2 or blank?