Change Multiple Table Array into one common Array

[Jay022]

Hi Mr. Excel,

Is there a way to change different table arrays from multiple vlookup to a one comman table array?

Let's say the formula looks like this:

=IFERROR(VLOOKUP($824,'Report'!$A:$BT, 72,0),0)+FERROR(VLOOKUP($824,'Report'!$A:$DJ,114,0),0)+FERROR(VLOOKUP($824, 'Report'!$A:$DQ,121,0),0)

The table arrays are:

$A:$BT
$A:$DJ
$A:$DQ

Since these are fixed arrays, I no longer need to lookup one by one, but just use a single table array $A:$DQ.

Is there a way or at least a tool in the ribbon? That can help me change these table arrays to $A:$DQ simultaneously?

 

[Domenic]

Is this what you mean?

=IFERROR(SUM(VLOOKUP($A2,Report!$A:$DQ,{72,114,121},0)),0)

If not, please clarify.

Cheers!

 

[Jay022]

Is this what you mean?

=IFERROR(SUM(VLOOKUP($A2,Report!$A:$DQ,{72,114,121},0)),0)

If not, please clarify.

Cheers!

It's actually much better formula haha, I'm new to formulas in excel thanks for this. However, I'm also looking for a way to change the different table arrays into $A:$DQ. I actually have a lot of columns to change and find related then change doesn't work hehe. If yiu could suggest anything on that area as well that would be great

 

[Domenic]

Sorry, but I still don't understand. Can you elaborate? Maybe provide an example?

 

[Jay022]

It's all good now Sir thanks, I used your formula

How about this one:

I would like the 80% to be may False value however it is being read as another logical

=AND(IF(AND(ABS="SCE/CE or AE",Z5=("HDW_CDCT" "SFW_CDCT" "SVC_ATTACH_CDCT")), 50% ), IF(AND(ABS="SCE/CE or AE",Z5 =("HDW_NONCDCT", "SFW_NONCDCT","SVC_ATTACH_NONCDCT")),100%), IF(AND ABS="SCE/CE or AE",25=("MTC_CDCT","MTC2_CDCT","BOOKING_MTC_CDCT")),20% ),80%)

Is there a way to make the 80% the false value?

 

[Domenic]

That's great, glad I was able to help.

For your new question, though, please start a new thread.

Thanks!